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