private void Check()
{
DataBaseWork.OpenCon();
string login = Convert.ToString(LoginText.Text);
string password = Convert.ToString(PassText.Text);
int i = 0;
string security = "SELECT * FROM access WHERE login = '******' AND password = '******';";
SqlCommand sc = new SqlCommand(security, DataBaseWork.Con);
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(sc);
sda.Fill(dt);
i = Convert.ToInt32(dt.Rows.Count.ToString());
if (i == 0)
{
MessageBox.Show("Ошибка автоизации");
}
else
{
Main main = new Main();
main.Show();
Hide();
}
DataBaseWork.CloseConnection();
}
private void ReportStart_Click(object sender, EventArgs e)
{
string report = "SELECT COUNT (*) AS [Общее количество договоров] FROM CONTRACTS WHERE ID_MANAGER = '" + Manager.id + "'";
string report1 = "SELECT * FROM CONTRACTS WHERE ID_ORDER = (SELECT * FROM ORDERS) AND ID_MANAGER = '" + Manager.id + "'";
res = DataBaseWork.Load(report1);
ReportView.DataSource = res;
}
private void UpdMasterBut_Click(object sender, EventArgs e)
{
DataBaseWork.OpenCon();
string save = String.Format("UPDATE {0} SET name = '{1}', surname = '{2}', dateofbirth = '{3}', phonenumber = '{4}',address = '{5}', passport = '{6}' WHERE customerid = '{7}'", "customers", SurnameText.Text, NameText.Text, dateTimePicker1.Value.Date, AddressText.Text, PhoneText.Text, SalaryText.Text, id);
SqlCommand com = new SqlCommand(save, DataBaseWork.Con);
com.ExecuteNonQuery();
MessageBox.Show("Данные о клиенте успешно изменены");
}
public static void AddClient(string surname, string name, string patronimyc, string address, string phone)
{
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO CLIENTS (C_SURNAME,C_NAME,C_PATRONYMIC,C_ADDRESS,C_NUMBER) VALUES ('" + surname + "', '" + name + "', '" + patronimyc + "', '" + address + "', '" + phone + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
}
public static void AddManager(string surname, string name, string patronimyc, string address, string phone, string salary, string login, string password)
{
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO MANAGERS (MANAGER_SURNAME,MANAGER_NAME,MANAGER_PATRONYMIC,MANAGER_ADDRESS, MANAGER_PHONE, MANAGER_SALARY,MANAGER_LOGIN, MANAGER_PASSWORD) VALUES ('" + surname + "', '" + name + "', '" + patronimyc + "', '" + address + "', '" + phone + "', '" + salary + "', '" + login + "', '" + password + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
}
public static void DeleteManager(DataGridView grid)
{
DataBaseWork.OpenCon();
var num = (int)grid.CurrentRow.Cells["ID"].Value;
SqlCommand sc = new SqlCommand(String.Format("DELETE FROM MANAGERS WHERE ID = {0}", num), DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
}
public static void UpdClient(string surname, string name, string patronimyc, string address, string phone)
{
DataBaseWork.OpenCon();
string add = String.Format("UPDATE {0} SET C_SURNAME = '{1}', C_NAME = '{2}', C_PATRONYMIC = '{3}', C_ADDRESS = '{4}', C_NUMBER = '{5}' WHERE ID = '{6}'", "CLIENTS", surname, name, patronimyc, address, phone);
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
}
private void AddButQual_Click(object sender, EventArgs e)
{
DataBaseWork.OpenCon();
string upd = String.Format("UPDATE {0} SET APPELATION = '{1}', Q_POWER = '{2}' WHERE ID = '{3}'", "QUALIFICATION", AText.Text, PText.Text, id);
SqlCommand sc = new SqlCommand(upd, DataBaseWork.Con);
sc.ExecuteNonQuery();
MessageBox.Show("Данные о квалификации успешно изменены.");
DataBaseWork.CloseConnection();
}
private void UpdManagerBut_Click(object sender, EventArgs e)
{
DataBaseWork.OpenCon();
string upd = String.Format("UPDATE {0} SET MANAGER_SURNAME = '{1}', MANAGER_NAME = '{2}', MANAGER_PATRONYMIC = '{3}',MANAGER_ADDRESS = '{4}',MANAGER_PHONE = '{5}',MANAGER_SALARY = '{6}',MANAGER_LOGIN = '******', MANAGER_PASSWORD = '******' WHERE ID = '{9}'", "MANAGERS", SurnameText.Text, NameText.Text, PatronymicText.Text, AddressText.Text, PhoneText.Text, SalaryText.Text, LoginText.Text, PasswordText.Text, id);
SqlCommand sc = new SqlCommand(upd, DataBaseWork.Con);
sc.ExecuteNonQuery();
MessageBox.Show("Данные менеджера успешно изменены.");
DataBaseWork.CloseConnection();
}
private void UpdTypeBut_Click(object sender, EventArgs e)
{
DataBaseWork.OpenCon();
string upd = String.Format("UPDATE {0} SET NAME_TYPE = '{1}' WHERE ID = '{2}'", "PRODUCT_TYPE", ApText.Text, id);
SqlCommand sc = new SqlCommand(upd, DataBaseWork.Con);
sc.ExecuteNonQuery();
MessageBox.Show("Данные типа изделий успешно изменены.");
DataBaseWork.CloseConnection();
}
private void delete_manager_Click(object sender, EventArgs e)
{
DataBaseWork.OpenCon();
var num = (int)manager_list.CurrentRow.Cells["managerid"].Value;
SqlCommand sc = new SqlCommand(String.Format("DELETE FROM managers WHERE managerid = {0}", num), DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Данные о менеджере успешно удалены");
}
private void AddMasterBut_Click(object sender, EventArgs e)
{
DataBaseWork.OpenCon();
string upd = String.Format("UPDATE {0} SET name = '{1}', surname = '{2}', dateofbirth = '{3}', phonenumber = '{4}', salary = '{5}', brunchid = '{6}' WHERE managerid = '{7}'", "managers", SurnameText.Text, NameText.Text, dateTimePicker1.Value.Date, AddressText.Text, PhoneText.Text, QualList.ValueMember);
SqlCommand sc = new SqlCommand(upd, DataBaseWork.Con);
sc.ExecuteNonQuery();
MessageBox.Show("Данные о менеджере успешно изменены.");
DataBaseWork.CloseConnection();
}
private void UpdMasterBut_Click(object sender, EventArgs e)
{
int q = Convert.ToInt32(QualList.SelectedValue);
DataBaseWork.OpenCon();
string save = String.Format("UPDATE {0} SET MAS_SURNAME = '{1}', MAS_NAME = '{2}',MAS_PATRONYMIC = '{3}', MAS_ADDRESS = '{4}', MAS_NUMBER = '{5}',ID_QUAL = {6},MAS_MONEY = '{7}' WHERE ID = '{8}'", "MASTERS", SurnameText.Text, NameText.Text, PatronimycText.Text, AddressText.Text, PhoneText.Text, q, SalaryText.Text, id);
SqlCommand com = new SqlCommand(save, DataBaseWork.Con);
com.ExecuteNonQuery();
MessageBox.Show("Данные о мастере успешно изменены");
}
private void UpdProductBut_Click(object sender, EventArgs e)
{
int t = Convert.ToInt32(TypeCombo.SelectedValue);
DataBaseWork.OpenCon();
string save = String.Format("UPDATE {0} SET APPELATION = '{1}', P_TYPE = {2},TIME_PROD = '{3}' WHERE ID = '{4}'", "PRODUCT", Appelation.Text, t, TimeProd.Text, id);
SqlCommand com = new SqlCommand(save, DataBaseWork.Con);
com.ExecuteNonQuery();
MessageBox.Show("Данные об изделии успешно изменены");
}
private void LoadCombo()
{
DataBaseWork.OpenCon();
SqlCommand sc = new SqlCommand("SELECT * FROM PRODUCT", DataBaseWork.Con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(sc);
da.Fill(dt);
ProdList.DataSource = dt;
ProdList.DisplayMember = "APPELATION";
ProdList.ValueMember = "ID";
ProdList.SelectedIndex = 0;
SqlCommand sc1 = new SqlCommand("SELECT * FROM MATERIALS", DataBaseWork.Con);
DataTable dt1 = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter(sc1);
da1.Fill(dt1);
MatList.DataSource = dt1;
MatList.DisplayMember = "APPELATION";
MatList.ValueMember = "ID";
SqlCommand sc2 = new SqlCommand("SELECT * FROM MASTERS", DataBaseWork.Con);
DataTable dt2 = new DataTable();
SqlDataAdapter da2 = new SqlDataAdapter(sc2);
da2.Fill(dt2);
MasList.DataSource = dt2;
MasList.DisplayMember = "MAS_SURNAME";
MasList.ValueMember = "ID";
SqlCommand sc3 = new SqlCommand("SELECT * FROM CLIENTS", DataBaseWork.Con);
DataTable dt3 = new DataTable();
SqlDataAdapter da3 = new SqlDataAdapter(sc3);
da3.Fill(dt3);
CliList.DataSource = dt3;
CliList.DisplayMember = "C_SURNAME";
CliList.ValueMember = "ID";
CliList.SelectedIndex = 0;
SqlCommand sc4 = new SqlCommand("SELECT * FROM ORDERS", DataBaseWork.Con);
DataTable dt4 = new DataTable();
SqlDataAdapter da4 = new SqlDataAdapter(sc4);
da4.Fill(dt4);
OrderNum.DataSource = dt4;
OrderNum.DisplayMember = "ID";
OrderNum.ValueMember = "ID";
DataBaseWork.CloseConnection();
}
private void AddMasterBut_Click(object sender, EventArgs e)
{
int q = Convert.ToInt32(QualList.SelectedValue);
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO managers (name, surname, dateofbirth, phonenumber, salary, brunchid ) VALUES ('" + SurnameText.Text + "', '" + NameText.Text + "', '" + dateTimePicker1.Value.Date + "', '" + AddressText.Text + "', '" + PhoneText.Text + "', " + q + ")");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Менеджер успешно добавлен");
}
private void AddMasterBut_Click(object sender, EventArgs e)
{
int q = Convert.ToInt32(QualList.SelectedValue);
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO MASTERS (MAS_SURNAME, MAS_NAME, MAS_PATRONYMIC, MAS_ADDRESS, MAS_NUMBER, ID_QUAL, MAS_MONEY) VALUES ('" + SurnameText.Text + "', '" + NameText.Text + "', '" + PatronimycText.Text + "', '" + AddressText.Text + "', '" + PhoneText.Text + "', " + q + ", '" + SalaryText.Text + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Мастер успешно добавлен");
}
private void AddProductBut_Click(object sender, EventArgs e)
{
int t = Convert.ToInt32(TypeCombo.SelectedValue);
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO PRODUCT (APPELATION,P_TYPE,TIME_PROD ) VALUES ('" + Appelation.Text + "', " + t + ", '" + TimeProd.Text + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Изделие успешно добавлено");
}
private void ContrBut_Click(object sender, EventArgs e)
{
int o = Convert.ToInt32(OrderNum.SelectedValue);
int client = Convert.ToInt32(CliList.SelectedValue);
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO CONTRACTS (ID_ORDER,ID_CLIENT, ID_MANAGER, DATE_CONTR) VALUES (" + o + ", " + client + ", " + Manager.id + ", '" + DateContr.Value.Date + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Договор успешно добавлен");
}
private void UpdMaterialBut_Click(object sender, EventArgs e)
{
decimal p = Convert.ToDecimal(price);
decimal q = Convert.ToDecimal(QuaText.Text);
DataBaseWork.OpenCon();
string upd = String.Format("UPDATE {0} SET APPELATION = '{1}', PRICE_GRAM = {2}, QUANTITY = {3} WHERE ID = '{4}'", "MATERIALS", ApText.Text, p, q, id);
SqlCommand sc = new SqlCommand(upd, DataBaseWork.Con);
sc.ExecuteNonQuery();
MessageBox.Show("Данные о материале успешно изменены.");
DataBaseWork.CloseConnection();
}
private void Save_Client_Click(object sender, EventArgs e)
{
try
{
DataBaseWork.OpenCon();
string save = String.Format("UPDATE {0} SET C_SURNAME = '{1}', C_NAME = '{2}',C_PATRONYMIC = '{3}', C_ADDRESS = '{4}', C_NUMBER = '{5}' WHERE ID = '{6}'", "CLIENTS", Surname_Client.Text, Name_Client.Text, Patronymic_Client.Text, Address_Client.Text, Phone_Client.Text, id);
SqlCommand com = new SqlCommand(save, DataBaseWork.Con);
com.ExecuteNonQuery();
MessageBox.Show("Успех!");
}
catch
{
MessageBox.Show("Error");
}
}
private void Save_Client_Click(object sender, EventArgs e)
{
try
{
DataBaseWork.OpenCon();
string save = String.Format("UPDATE {0} SET address = '{1}', contactphone = '{2}' WHERE brunchid = '{3}'", "brunches", Surname_Client.Text, Name_Client.Text, id);
SqlCommand com = new SqlCommand(save, DataBaseWork.Con);
com.ExecuteNonQuery();
MessageBox.Show("Успех!");
}
catch
{
MessageBox.Show("Error");
}
}
private void Save_Client_Click(object sender, EventArgs e)
{
try
{
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO brunches (address, contactphone) VALUES ('" + address_brunch.Text + "', '" + conphone_brunch.Text + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Филиал успешно добавлен в базу.");
}
catch
{
MessageBox.Show("Ошибка добавления.\nПопробуйте ещё раз");
}
}
private void AddTypeBut_Click(object sender, EventArgs e)
{
try
{
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO PRODUCT_TYPE (NAME_TYPE) VALUES ('" + ApText.Text + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Тип успешно добавлен в базу.");
}
catch
{
MessageBox.Show("При доавлении типа изделий произошла ошибка.\nПопробуйте ещё раз.");
}
}
private void AddManager_Click(object sender, EventArgs e)
{
try
{
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO customers (name, surname, dateofbirth, phonenumber, address, passport ) VALUES ('" + SurnameText.Text + "', '" + NameText.Text + "', '" + date_birth.Value.Date + "', '" + AddressText.Text + "', '" + PhoneText.Text + "', '" + SalaryText.Text + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Клиент успешно добавлен в базу.");
}
catch
{
MessageBox.Show("Ошибка добавления.\nПопробуйте ещё раз");
}
}
private void AddButQual_Click(object sender, EventArgs e)
{
try
{
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO QUALIFICATION (APPELATION, Q_POWER) VALUES ('" + AText.Text + "', '" + PText.Text + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Квалификация успешно добавлена в базу.");
}
catch
{
MessageBox.Show("Error");
}
}
private void OrderBut_Click(object sender, EventArgs e)
{
int p = Convert.ToInt32(ProdList.SelectedValue);
int mat = Convert.ToInt32(MatList.SelectedValue);
int mas = Convert.ToInt32(MasList.SelectedValue);
decimal pr = Convert.ToDecimal(PriceEnd.Text);
string time = TimeProd.Text;
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO ORDERS (ID_PRODUCT,ID_MATERIAL, ID_MASTER, PRICE, TIME_PROD ) VALUES (" + p + ", " + mat + ", " + mas + ", " + pr + ", '" + time + "')");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Заказ успешно добавлен");
}
private void AddMaterialBut_Click(object sender, EventArgs e)
{
try
{
decimal pr = Convert.ToDecimal(PrText.Text);
decimal q = Convert.ToDecimal(QuaText.Text);
DataBaseWork.OpenCon();
string add = String.Format("INSERT INTO MATERIALS (APPELATION, PRICE_GRAM,QUANTITY ) VALUES ('" + ApText.Text + "', " + pr + ", " + q + ")");
SqlCommand sc = new SqlCommand(add, DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Материал успешно добавлен в базу.");
}
catch
{
MessageBox.Show("Ошибка при добавлении материала.\nПопробуйте ещё раз.");
}
}
private void Delete_Product_Click(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("Вы точно хотите удалить запись?", "Удаление", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
DataBaseWork.OpenCon();
var num = (int)ProductList.CurrentRow.Cells["ID"].Value;
SqlCommand sc = new SqlCommand(String.Format("DELETE FROM PRODUCT WHERE ID = {0}", num), DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Изделие удалено.");
Main.Loading("SELECT * FROM PRODUCT", ProductList);
}
}
catch
{
MessageBox.Show("Ошибка удаления");
}
}
private void Delete_Client_Click(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("Вы точно хотите удалить запись?", "Deleting", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
DataBaseWork.OpenCon();
var num = (int)brunch_list.CurrentRow.Cells["brunchid"].Value;
SqlCommand sc = new SqlCommand(String.Format("DELETE FROM brunches WHERE brunchid = {0}", num), DataBaseWork.Con);
sc.ExecuteNonQuery();
DataBaseWork.CloseConnection();
MessageBox.Show("Данные о филиале удалены");
Loading("SELECT * FROM brunches", brunch_list);
}
}
catch
{
MessageBox.Show("Ошибка удаления");
}
}