private void Change_employee_button_Click(object sender, EventArgs e) { DB dB = new DB(); int id = int.Parse(str[0]); string name = FIO_employee.Text; string birthdate = dateTimePicker1.Text; string gender = gender_employee.Text; string mp = Mob_phone_employee.Text; string position = Position_richtextbox.Text; string adress = Adress_employee.Text; string workplace = Workplace.SelectedItem.ToString(); string wages = wages_employee.Text; dB.openConnection(); MySqlCommand command = new MySqlCommand($"SELECT DISTINCT BRANCH_ID FROM branch_office WHERE `BRANCH_NAME` = '{workplace}'", dB.getConnection()); MySqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { object ID = reader.GetValue(0); workplace = ID.ToString(); } } reader.Close(); command.CommandText = "UPDATE `employes` SET `EMPLOYEE_NAME` = @name, `BIRTHDATE` = @bd, `GENDER` = @gend," + " `MOBILE_PHONE` = @mobphone, `ADDRESS` = @adress, `POSITION` = @pos, `WAGES` = @wages," + " `WORKPLACE` = @workplace WHERE `employes`.`EMPLOYEE_ID` = @id "; command.Parameters.Add("@name", MySqlDbType.VarChar).Value = name; command.Parameters.Add("@bd", MySqlDbType.Date).Value = birthdate; command.Parameters.Add("@gend", MySqlDbType.VarChar).Value = gender; command.Parameters.Add("@mobphone", MySqlDbType.VarChar).Value = mp; command.Parameters.Add("@adress", MySqlDbType.VarChar).Value = adress; command.Parameters.Add("@pos", MySqlDbType.VarChar).Value = position; command.Parameters.Add("@wages", MySqlDbType.Int32).Value = wages; command.Parameters.Add("@workplace", MySqlDbType.Int32).Value = workplace; command.Parameters.Add("@id", MySqlDbType.Int32).Value = id; if (command.ExecuteNonQuery() == 1) { MessageBox.Show("Изменения внесены в базу!"); this.Close(); } else { MessageBox.Show("Произошла ошибка!"); } dB.closeConnection(); }
public Change_client(List <string> str) { InitializeComponent(); this.str = str; dateTimePicker1.Format = DateTimePickerFormat.Custom; dateTimePicker1.CustomFormat = "yyyy-MM-dd"; dateTimePicker2.Format = DateTimePickerFormat.Custom; dateTimePicker2.CustomFormat = "yyyy-MM-dd"; DB dB = new DB(); string sql = "SELECT DISTINCT employes.EMPLOYEE_NAME FROM employes"; dB.openConnection(); MySqlCommand command = new MySqlCommand(sql, dB.getConnection()); MySqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { object name = reader.GetValue(0); Contracter.Items.Add(name); } } reader.Close(); command.CommandText = "SELECT DISTINCT type_loan.LOAN_NAME FROM type_loan"; reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { object name = reader.GetValue(0); type_loan.Items.Add(name); } } reader.Close(); dB.closeConnection(); FIO_client.Text = str[1]; dateTimePicker2.Text = str[2]; gender_client.Text = str[3]; Mob_phone_client.Text = str[4]; Adress_client.Text = str[5]; Contracter.Text = str[6]; type_loan.Text = str[7]; dateTimePicker1.Text = str[8]; wages_clients.Text = str[9]; }
private void delete_client_button_Click(object sender, EventArgs e) { List <string> ID = new List <string>(); bool check = false; DB dB = new DB(); for (int i = 0; i < Clients_base.Rows.Count; i++) { if (this.Clients_base.Rows[i].Cells[0].Value != null) { check = true; ID.Add(Clients_base.Rows[i].Cells[1].Value.ToString()); } } for (int i = 0; i < Clients_base.Rows.Count; i++) { Clients_base.Rows[i].Cells[0].Value = null; } if (!check) { MessageBox.Show("Не выбраны записи!"); } else { string sql = "DELETE FROM `client` WHERE "; foreach (var s in ID) { sql += "CLIENT_ID = " + s + " OR "; } sql = sql.Substring(0, sql.Length - 4); dB.openConnection(); MySqlCommand command = new MySqlCommand(sql, dB.getConnection()); if (command.ExecuteNonQuery() != -1) { MessageBox.Show("Записи удалены из базы!"); } else { MessageBox.Show("Произошла ошибка!"); } dB.closeConnection(); } }
public Register_сlient() { InitializeComponent(); dateTimePicker1.Format = DateTimePickerFormat.Custom; dateTimePicker1.CustomFormat = "yyyy-MM-dd"; dateTimePicker2.Format = DateTimePickerFormat.Custom; dateTimePicker2.CustomFormat = "yyyy-MM-dd"; DB dB = new DB(); string sql = "SELECT DISTINCT employes.EMPLOYEE_NAME FROM employes"; dB.openConnection(); MySqlCommand command = new MySqlCommand(sql, dB.getConnection()); MySqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { object name = reader.GetValue(0); Contracter.Items.Add(name); } } reader.Close(); command.CommandText = "SELECT DISTINCT type_loan.LOAN_NAME FROM type_loan"; reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { object name = reader.GetValue(0); type_loan.Items.Add(name); } } reader.Close(); dB.closeConnection(); }
private void Update_button_Click(object sender, EventArgs e) { DB dB = new DB(); string sql = "SELECT client.CLIENT_ID,client.CLIENT_NAME 'Имя', DATE_FORMAT(client.BIRTHDATE,'%Y-%m-%d') as 'Дата рождения', client.GENDER 'Пол', " + "client.MOBILE_PHONE 'Моб.телефон', client.ADDRESS 'Адрес'," + "employes.EMPLOYEE_NAME 'Контрактер',type_loan.LOAN_NAME 'Вид займа',DATE_FORMAT(client.DATA_AGR,'%Y-%m-%d') as 'Дата заключения договора',client.WAGES 'Зарплата'" + " FROM `client` INNER JOIN `employes` ON client.CONTRACTER = employes.EMPLOYEE_ID INNER JOIN `type_loan` ON client.TYPE_LOAN = type_loan.LOAN_ID"; Clients_base.Columns[1].Visible = false; Clients_base.AllowUserToAddRows = false; dB.openConnection(); MySqlDataAdapter adapter = new MySqlDataAdapter(sql, dB.getConnection()); DataSet ds = new DataSet(); adapter.Fill(ds); Clients_base.DataSource = ds.Tables[0]; dB.closeConnection(); Clients_base.Columns[2].Width = 250; Clients_base.Columns[4].Width = 50; Clients_base.Columns[5].Width = 150; Clients_base.Columns[6].Width = 250; Clients_base.Columns[7].Width = 200; Clients_base.Columns[8].Width = 200; }
private void delete_emp_button_Click(object sender, EventArgs e) { List <string> ID = new List <string>(); bool check = false; DB dB = new DB(); for (int i = 0; i < Employes_base.Rows.Count; i++) { if (this.Employes_base.Rows[i].Cells[0].Value != null) { check = true; ID.Add(Employes_base.Rows[i].Cells[1].Value.ToString()); } } for (int i = 0; i < Employes_base.Rows.Count; i++) { Employes_base.Rows[i].Cells[0].Value = null; } if (!check) { MessageBox.Show("Не выбраны записи!"); } else { string sql_check = "SELECT * FROM `client` WHERE CONTRACTER = "; string sql = "DELETE FROM `employes` WHERE "; bool allow = true; foreach (var s in ID) { sql += "EMPLOYEE_ID = " + s + " OR "; } sql = sql.Substring(0, sql.Length - 4); dB.openConnection(); foreach (var s in ID) { MySqlCommand command1 = new MySqlCommand(sql_check + s, dB.getConnection()); MySqlDataReader reader = command1.ExecuteReader(); if (reader.HasRows) { allow = false; } reader.Close(); } if (allow) { MySqlCommand command = new MySqlCommand(sql, dB.getConnection()); if (command.ExecuteNonQuery() != -1) { MessageBox.Show("Записи удалены из базы!"); } else { MessageBox.Show("Произошла ошибка!"); } } else { MessageBox.Show("Нельзя удалить запись, т.к. с ним связан клиент!"); } dB.closeConnection(); } }
private void Change_client_button_Click(object sender, EventArgs e) { DB dB = new DB(); int id = int.Parse(str[0]); string name = FIO_client.Text; string birthdate = dateTimePicker2.Text; string gender = gender_client.Text; string mp = Mob_phone_client.Text; string adress = Adress_client.Text; string contracter = Contracter.SelectedItem.ToString(); string typeLoan = type_loan.SelectedItem.ToString(); string data = dateTimePicker1.Text; string wages = wages_clients.Text; dB.openConnection(); MySqlCommand command = new MySqlCommand($"SELECT DISTINCT EMPLOYEE_ID FROM employes WHERE `EMPLOYEE_NAME` = '{contracter}'", dB.getConnection()); MySqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { object ID = reader.GetValue(0); contracter = ID.ToString(); } } reader.Close(); command.CommandText = $"SELECT DISTINCT LOAN_ID FROM type_loan WHERE `LOAN_NAME` = '{typeLoan}'"; reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { object ID = reader.GetValue(0); typeLoan = ID.ToString(); } } reader.Close(); command.CommandText = "UPDATE `client` SET `CLIENT_NAME` = @name, `BIRTHDATE` = @bd, `GENDER` = @gend," + " `MOBILE_PHONE` = @mobphone, `ADDRESS` = @adress, `CONTRACTER` = @cont, `TYPE_LOAN` = @loan," + " `DATA_AGR` = @dagr, `WAGES` = @wages WHERE `client`.`CLIENT_ID` = @id "; command.Parameters.Add("@name", MySqlDbType.VarChar).Value = name; command.Parameters.Add("@bd", MySqlDbType.Date).Value = birthdate; command.Parameters.Add("@gend", MySqlDbType.VarChar).Value = gender; command.Parameters.Add("@mobphone", MySqlDbType.VarChar).Value = mp; command.Parameters.Add("@adress", MySqlDbType.VarChar).Value = adress; command.Parameters.Add("@cont", MySqlDbType.Int32).Value = contracter; command.Parameters.Add("@loan", MySqlDbType.Int32).Value = typeLoan; command.Parameters.Add("@dagr", MySqlDbType.Date).Value = data; command.Parameters.Add("@wages", MySqlDbType.Int32).Value = wages; command.Parameters.Add("@id", MySqlDbType.Int32).Value = id; if (command.ExecuteNonQuery() == 1) { MessageBox.Show("Изменения внесенЫ в базу!"); this.Close(); } else { MessageBox.Show("Произошла ошибка!"); } dB.closeConnection(); }