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();
}