private void AddUser_Click(object sender, EventArgs e)
{
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" &&
textBox4.Text != "" && comboBox1.SelectedIndex != -1 && textBox6.Text != "")
{
DataT DataT = new DataT();
DataTable dt_u = DataT.Select("Select MAX(ID) FROM [dbo].[Users]");
//генерируем id - находим последний номер + 1
int idU = Convert.ToInt32(dt_u.Rows[0][0].ToString()) + 1;
//индекс офиса - индекс элемента из выпадающего списка + 1
int of = comboBox1.SelectedIndex + 1;
try
{
DataTable dt_users = DataT.Select("Insert INTO [dbo].[Users] VALUES('" + idU + "','2', '" + textBox1.Text + "','" +
textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + of + "','" + Convert.ToDateTime(textBox6.Text) + "', 'True')");
MessageBox.Show("Сотрудник успешно добавлен", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Заполните все поля коректно!", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("Заполните все поля!", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
//преобразования данных из таблицы в датагрид
void setData()
{
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
DataT DataT = new DataT();
DataTable dt_airports = DataT.Select("SELECT * FROM [dbo].[Airports]");
if (dt_airports.Rows.Count > 0)
{
for (int j = 0; j < dt_airports.Rows.Count; j++)
{
if (dataGridView1.Rows[i].Cells[3].Value != null)
{
if (dataGridView1.Rows[i].Cells[3].Value.ToString() == dt_airports.Rows[j][0].ToString())
{
dataGridView1.Rows[i].Cells[4].Value = dt_airports.Rows[j][2].ToString();
}
}
if (dataGridView1.Rows[i].Cells[5].Value != null)
{
if (dataGridView1.Rows[i].Cells[5].Value.ToString() == dt_airports.Rows[j][0].ToString())
{
dataGridView1.Rows[i].Cells[6].Value = dt_airports.Rows[j][2].ToString();
}
}
}
}
dataGridView1.Rows[i].Cells[10].Value = Math.Round(Convert.ToDouble(dataGridView1.Rows[i].Cells[10].Value), 0);
dataGridView1.Rows[i].Cells[11].Value = Math.Round(Convert.ToDouble(dataGridView1.Rows[i].Cells[10].Value) * 1.35, 0);
dataGridView1.Rows[i].Cells[12].Value = Math.Round(Convert.ToDouble(dataGridView1.Rows[i].Cells[11].Value) * 1.3, 0);
if (Convert.ToBoolean(dataGridView1.Rows[i].Cells[7].Value) == false)
{
dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.FromArgb(255, 46, 46);
}
}
}
private void BtnApply_Click(object sender, EventArgs e)
{
string newId = "";
try
{
if (rbAdm.Checked == true)
{
newId = "1";
}
if (rbUser.Checked == true)
{
newId = "2";
}
DataT DataT = new DataT();
DataTable dt_user = DataT.Select("UPDATE [dbo].[Users] SET RoleID = '" + newId + "' WHERE [Email] ='" + email + "'");
MessageBox.Show("Успешно изменено", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Изменение не удалось. Выберите роль для сотрудника",
"Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public EditRole(string mail)
{
InitializeComponent();
email = mail;
DataT DataT = new DataT();
DataTable dt_user = DataT.Select("SELECT * FROM [dbo].[Users] WHERE [Email] =" +
" '" + email + "'");
if (dt_user.Rows.Count > 0)
{
textBox1.Text = dt_user.Rows[0][2].ToString();
textBox3.Text = dt_user.Rows[0][4].ToString();
textBox4.Text = dt_user.Rows[0][5].ToString();
//офис, опредление офиса
DataTable dt_office = DataT.Select("SELECT * FROM [dbo].[Offices] WHERE ID = '" + dt_user.Rows[0][6].ToString() + "'");
if (dt_office.Rows.Count > 0)
{
textBox2.Text = dt_office.Rows[0][2].ToString();
}
if (dt_user.Rows[0][1].ToString() == "1")
{
rbAdm.Checked = true;
}
else if (dt_user.Rows[0][1].ToString() == "2")
{
rbUser.Checked = true;
}
}
}
public NoLogOut(string id, string logIn)
{
InitializeComponent();
label1.Text = label1.Text + logIn;
ident = id;
//вносим в причину часть с re
DataT DataT = new DataT();
DataTable dt_track = DataT.Select("UPDATE [dbo].[Tracking] SET Reason = '"
+ reason + "', LogOut = '" + DateTime.Now + "'" +
" WHERE [ID] ='" + ident + "'");
}
private void Button2_Click(object sender, EventArgs e)
{
//запись в трэкинг выхода из системы
DataT DataT = new DataT();
DataTable dt_user = DataT.Select("SELECT * FROM [dbo].[Users] WHERE [Email] =" +
" '" + user + "'");
DataTable dt_trackID = DataT.Select("Select MAX(ID) FROM [dbo].[Tracking] WHERE UserID = '" + dt_user.Rows[0][0].ToString() + "'");
DataTable dt_track = DataT.Select("UPDATE [dbo].[Tracking] SET LogOut = '" + DateTime.Now + "'" +
" WHERE [ID] ='" + dt_trackID.Rows[0][0].ToString() + "'");
this.Close();
}
public scrUser(string login)
{
user = login;
InitializeComponent();
DataT DataT = new DataT();
DataTable dt_user = DataT.Select("SELECT * FROM [dbo].[Users] WHERE [Email] =" +
" '" + login + "'");
if (dt_user.Rows.Count > 0)
{
label1.Text = "Hi " + dt_user.Rows[0][4] + ", Welcome to Amonic Airlines.";
}
}
private void ComboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
int row = Convert.ToInt32(comboBox1.SelectedIndex);
DataT DataT = new DataT();
DataTable dt_office = DataT.Select("SELECT * FROM [dbo].[Offices]");
if (row != -1)
{
this.usersTableAdapter1.FillBy(this.session2_TankaevaDataSet.Users, Convert.ToInt32(dt_office.Rows[row][0]));
MessageBox.Show("Офис успешно отфильтрован", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
FillGrid();
}
private void BtnUpdate_Click(object sender, EventArgs e)
{
try
{
DataT DataT = new DataT();
DataTable dt_schdls = DataT.Select("UPDATE [dbo].[Schedules] SET Date = '" + Convert.ToDateTime(textBox1.Text) + "'," +
" Time = '" + Convert.ToDateTime(textBox2.Text) + "', EconomyPrice ='" + textBox3.Text + "' WHERE [ID] =" +
" '" + id + "'");
MessageBox.Show("Updated succesfully", "Info",
MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch
{
MessageBox.Show("Cant update. Check entered format", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public schdlEd(string _id, string from, string to, string air)
{
InitializeComponent();
id = _id;
label2.Text = from;
label4.Text = to;
label6.Text = air;
DataT DataT = new DataT();
DataTable dt_schdls = DataT.Select("SELECT * FROM [dbo].[Schedules] WHERE [ID] =" +
" '" + id + "'");
if (dt_schdls.Rows.Count > 0)
{
textBox1.Text = dt_schdls.Rows[0][1].ToString();
textBox3.Text = dt_schdls.Rows[0][5].ToString();
textBox2.Text = dt_schdls.Rows[0][2].ToString();
}
}
void FillGrid()
{
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
DataT DataT = new DataT();
//определение ролей
DataTable dt_roles = DataT.Select("SELECT * FROM [dbo].[Roles]");
if (dt_roles.Rows.Count > 0)
{
for (int r = 0; r < dt_roles.Rows.Count; r++)
{
if (dataGridView1.Rows[i].Cells[6].Value.ToString() == dt_roles.Rows[r][0].ToString())
{
dataGridView1.Rows[i].Cells[3].Value = dt_roles.Rows[r][1].ToString();
}
}
}
//офис, опредление офиса
DataTable dt_office = DataT.Select("SELECT * FROM [dbo].[Offices]");
if (dt_office.Rows.Count > 0)
{
for (int j = 0; j < dt_office.Rows.Count; j++)
{
if (dataGridView1.Rows[i].Cells[8].Value.ToString() == dt_office.Rows[j][0].ToString())
{
dataGridView1.Rows[i].Cells[5].Value = dt_office.Rows[j][2].ToString();
}
}
}
//подсчет возраста
DateTime d2 = Convert.ToDateTime(DateTime.Now);
DateTime d1 = Convert.ToDateTime(dataGridView1.Rows[i].Cells[7].Value);
dataGridView1.Rows[i].Cells[2].Value = (Math.Round(((d2 - d1).TotalDays / 365), 0));
if (dataGridView1.Rows[i].Cells[9].Value.ToString() == "False")
{
dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.FromArgb(255, 46, 46);
}
}
}
private void BtnConfirm_Click(object sender, EventArgs e)
{
DataT DataT = new DataT();
if (rbSoft.Checked == true)
{
reason = "Software crush;";
}
if (rbSystem.Checked == true)
{
reason += "System crush;";
}
reason += textBox1.Text;
DataTable dt_track = DataT.Select("UPDATE [dbo].[Tracking] SET Reason = '"
+ reason + "', LogOut = '" + DateTime.Now + "'" +
" WHERE [ID] ='" + ident + "'");
MessageBox.Show("Reason succesfully updated. Thank you!", "Attention!",
MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
//enable/disable
private void Button4_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow row in dataGridView1.SelectedRows)
{
string temp;
if (dataGridView1.Rows[row.Index].Cells[9].Value.ToString() == "False")
{
temp = "True";
}
else
{
temp = "False";
}
DataT DataT = new DataT();
DataTable dt_users = DataT.Select("UPDATE [dbo].[Users] SET [Active] = '" + temp + "' Where " +
"ID = '" + dataGridView1.Rows[row.Index].Cells[10].Value.ToString() + "'");
this.usersTableAdapter1.Fill(this.session2_TankaevaDataSet.Users);
FillGrid();
MessageBox.Show("Успешно изменено", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void BtnImport_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
textBox1.Text = openFileDialog1.FileName;
}
int insert = 0;
int fail = 0;
try
{
using (StreamReader sr = new StreamReader(textBox1.Text))
{
string line;
while (!sr.EndOfStream)
{
line = sr.ReadLine();
if (line.Split(',')[0] == "ADD")
{
try
{
DataT DataT = new DataT();
//id аэропортов
DataTable dt_from = DataT.Select("SELECT ID FROM [dbo].[Airports] WHERE(IATACode = '" + line.Split(',')[4] + "')");
DataTable dt_to = DataT.Select("SELECT ID FROM [dbo].[Airports] WHERE(IATACode = '" + line.Split(',')[5] + "')");
//id маршрута
DataTable dt_routes = DataT.Select("SELECT ID FROM [dbo].[Routes] WHERE (DepartureAirportID =" +
" '" + dt_from.Rows[0][0].ToString() + "') AND (ArrivalAirportID = '" + dt_to.Rows[0][0].ToString() + "')");
string conf = "true";
if (line.Split(',')[8] != "OK")
{
conf = "false";
}
DataTable dt_schdls = DataT.Select("INSERT INTO [dbo].[Schedules] Values ('" + line.Split(',')[1] +
"','" + line.Split(',')[2] + "','" + line.Split(',')[6] + "','" + dt_routes.Rows[0][0].ToString() +
"','" + line.Split(',')[7] + "','" + conf + "','" + line.Split(',')[3] + "')");
insert++;
}
catch
{
fail++;
}
}
if (line.Split(',')[0] == "EDIT")
{
try
{
DataT DataT = new DataT();
//id аэропортов
DataTable dt_from = DataT.Select("SELECT ID FROM [dbo].[Airports] WHERE(IATACode = '" + line.Split(',')[4] + "')");
DataTable dt_to = DataT.Select("SELECT ID FROM [dbo].[Airports] WHERE(IATACode = '" + line.Split(',')[5] + "')");
//id маршрута
DataTable dt_routes = DataT.Select("SELECT ID FROM [dbo].[Routes] WHERE (DepartureAirportID =" +
" '" + dt_from.Rows[0][0].ToString() + "') AND (ArrivalAirportID = '" + dt_to.Rows[0][0].ToString() + "')");
string conf = "true";
if (line.Split(',')[8] != "OK")
{
conf = "false";
}
DataTable dt_schdls = DataT.Select("UPDATE [dbo].[Schedules] SET Date = '" + line.Split(',')[1] +
"',Time = '" + line.Split(',')[2] + "', AircraftID ='" + line.Split(',')[6] + "', RouteID ='" + dt_routes.Rows[0][0].ToString() +
"',EconomyPrice ='" + line.Split(',')[7] + "',Confirmed = '" + conf + "', FlightNumber = '" + line.Split(',')[3] + "'" +
" WHERE Date = '" + line.Split(',')[1] + "' AND FlightNumber = '" + line.Split(',')[3] + "'");
insert++;
}
catch
{
fail++;
}
}
}
MessageBox.Show("Reading ended", "Info",
MessageBoxButtons.OK, MessageBoxIcon.Information);
label2.Text = fail.ToString();
label3.Text = insert.ToString();
}
}
catch
{
}
}
private void Login_Click(object sender, EventArgs e)
{
if (log.Text.Length > 0)
{
if (pass.Text.Length > 0)
{
//подключение к базе обращаясь к методу из program.cs
DataT DataT = new DataT();
DataTable dt_user = DataT.Select("SELECT * FROM [dbo].[Users] WHERE [Email] =" +
" '" + log.Text + "' AND [Password] = '" + pass.Text + "'");
if (dt_user.Rows.Count > 0)
{
if (dt_user.Rows[0][8].ToString() != "False")
{
string role = Convert.ToString(dt_user.Rows[0][1]);
string email = Convert.ToString(dt_user.Rows[0][2]);
//id для трекинга
DataTable dt_track = DataT.Select("Select MAX(ID) FROM [dbo].[Tracking]");
int idTrack;
try
{
idTrack = Convert.ToInt32(dt_track.Rows[0][0].ToString()) + 1;
}
catch
{
idTrack = 1;
}
//проверка на наличие данных о выходе из системы последний раз
DataTable dt_lastID = DataT.Select("Select MAX(ID) FROM [dbo].[Tracking] WHERE UserID = '" + dt_user.Rows[0][0].ToString() + "'");
DataTable dt_lastTrack = DataT.Select("Select * FROM [dbo].[Tracking] WHERE ID = '" + dt_lastID.Rows[0][0].ToString() + "'");
if (dt_lastTrack.Rows.Count > 0)
{
string logOut = dt_lastTrack.Rows[0][3].ToString();
if (logOut == "")
{
NoLogOut nlg = new NoLogOut(dt_lastID.Rows[0][0].ToString(), dt_lastTrack.Rows[0][2].ToString());
nlg.ShowDialog();
}
}
//запись нового входа
DataTable dt_users = DataT.Select("Insert INTO [dbo].[Tracking] VALUES ('" + idTrack + "'," +
"'" + dt_user.Rows[0][0].ToString() + "','" + DateTime.Now + "', null, null)");
if (role == "1")
{
//админ
scrAdm adm = new scrAdm(email);
adm.ShowDialog();
}
if (role == "2")
{
//пользователь
scrUser user = new scrUser(email);
user.ShowDialog();
}
if (role == "3")
{
//менеджер
scrMngr mngr = new scrMngr(email);
mngr.ShowDialog();
}
}
else
{
MessageBox.Show("Пользователь заблокирован!",
"Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("Пользователя не найден", "Ошибка!",
MessageBoxButtons.OK, MessageBoxIcon.Error);
i++;
if (i == 3)
{
MessageBox.Show("Вы исчерпали лимит попыток, доступ " +
"к программе заблокирован на 10 секунд.", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
login.Enabled = false;
timer1.Start();
}
;
}
}
else
{
MessageBox.Show("Введите пароль", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("Введите логин", "Внимание!",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}