private void searchTextBox_TextChanged(object sender, EventArgs e)
{
if (searchTextBox.Text != "Поиск по фамилии")
{
dataGridView1.DataSource = RequestsSQLT.SelectAllFromCustomerWithSetSurname(conn, searchTextBox);
}
}
public void Refresh4()
{
OpenConnectionCorrect(conn);
dataGridView4.DataSource = RequestsSQLT.SelectAllFromApartments(conn);
ElementsSettings.SetDefaultSettingsToDGV(dataGridView4);
ElementsSettings.RenameApartmentsDGV(dataGridView4);
}
public void Refresh1()
{
OpenConnectionCorrect(conn);
dataGridView1.DataSource = RequestsSQLT.SelectAllFromCustomer(conn);
ElementsSettings.SetDefaultSettingsToDGV(dataGridView1);
ElementsSettings.RenameCustomerDGV(dataGridView1);
}
public Form1()
{
InitializeComponent();
try
{
conn.Open();
MessageBox.Show("Connection successful!");
conn.Close();
}
catch (Exception e)
{
MessageBox.Show("Error: " + e.Message);
}
ElementsSettings.SearchTextBoxSettings(searchTextBox);
//ElementsSettings.DateTextBoxSettings(textBox7);
OpenConnectionCorrect(conn);
RequestsSQLT.GetTypesOfApartments(RequestsSQLT.comboElements, conn); // Получаем все реально имеющиеся типы номеров для данной гостинницы для последующего использоваия в ComboBox
discount = RequestsSQLT.GetCurrentDiscount(conn);
label19.Text = discount.ToString() + " %";
typeComboBox.Items.AddRange(admissibleTypesOfApartments);
SetButtonsInactive0();
SetButtonsInactive1();
SetButtonsInactive2();
SetButtonsInactive3();
}
private void changeApartmentsButton_Click(object sender, EventArgs e)
{
var dResult = MessageBox.Show("Вы уверены, что хотите применить изменения в базе данных?", "", MessageBoxButtons.YesNo);
if (dResult == DialogResult.Yes)
{
if (RequestsSQLT.NumberOfApartmentsIsFree(conn, numberOfChosenRow) == true) //Проверяем все ограничения ввода для таблицы клиентов
{
if (RequestsSQLT.SelectSetValueOfNumberFromApartments(numberTextBox, conn) == true)
{
if (RequestsSQLT.TypeOfApartmentsIsCorrect(typeComboBox, conn) == true)
{
SqlCommand command = new SqlCommand();
command = new SqlCommand($"UPDATE Apartments SET number = {Convert.ToInt32(numberTextBox.Text)}, \"type\" = '{typeComboBox.Text}', price = {Convert.ToInt32(priceTextBox.Text)} WHERE number = {Convert.ToInt32(numberTextBox.Text)}", conn);
command.ExecuteNonQuery();
Refresh4();
}
else
{
MessageBox.Show("Введён некорректный тип номера");
}
}
}
else
{
MessageBox.Show("Редактирование данной записи недоступно. На номер зарегистрированно неоконченное проживание или оформлена бронь.");
}
}
}
private void applyFiltresButton_Click(object sender, EventArgs e)
{
if (bottomPriceTextBox.Text != "")
{
smallestPrice = Convert.ToInt32(bottomPriceTextBox.Text);
}
else
{
smallestPrice = 0;
}
if (topPriceTextBox.Text != "")
{
biggestPrice = Convert.ToInt32(topPriceTextBox.Text);
}
else
{
biggestPrice = 100000;
}
if (typeOfApartmentsComboBox.SelectedItem == null)
{
dataGridViewANC.DataSource = RequestsSQLT.ApplyTargetFiltres(Form1.conn, null, searchebleSettlingDateTimePicker.Value, searchebleEvictionDateTimePicker.Value, smallestPrice, biggestPrice);
}
else
{
dataGridViewANC.DataSource = RequestsSQLT.ApplyTargetFiltres(Form1.conn, typeOfApartmentsComboBox.SelectedItem.ToString(), searchebleSettlingDateTimePicker.Value, searchebleEvictionDateTimePicker.Value, smallestPrice, biggestPrice);
}
ancSettlingDateTimePicker.Value = searchebleSettlingDateTimePicker.Value;
ancEvictionDateTimePicker.Value = searchebleEvictionDateTimePicker.Value;
}
public AddingNewCustomer()
{
InitializeComponent();
textBox1.Text = Form1.textBox2StaticValue;
textBox2.Text = Form1.textBox3StaticValue;
textBox3.Text = Form1.textBox4StaticValue;
textBox4.Text = Form1.textBox5StaticValue;
textBox5.Text = Form1.textBox6StaticValue;
ancBirthdayDateTimePicker.Value = Form1.dateTimePicker1StaticValue.Value;
textBox7.Text = Form1.textBox8StaticValue;
Form1.OpenConnectionCorrect(Form1.conn);
ElementsSettings.SetDefaultSettingsToDGV(dataGridViewANC);
dataGridViewANC.DataSource = RequestsSQLT.SelectAllFromApartments(Form1.conn);
for (int i = 0; i < RequestsSQLT.comboElements.Count; i++)
{
typeOfApartmentsComboBox.Items.Add(RequestsSQLT.comboElements[i]);
}
discountTextBox.Text = Form1.discount.ToString();
if (Form1.cb == true)
{
ancSettlingDateTimePicker.Value = Convert.ToDateTime(Form1.bookingS[2]);
ancEvictionDateTimePicker.Value = Convert.ToDateTime(Form1.bookingS[3]);
textBox13.Text = Form1.bookingS[5];
textBox14.Text = Form1.bookingS[6];
dataGridViewANC.DataSource = RequestsSQLT.SelectAllFromApartmentsWhereNumberIsSet(Form1.conn, Convert.ToInt32(Form1.bookingS[4]));
}
ElementsSettings.RenameApartmentsDGV(dataGridViewANC);
CheckAndAddCustomerData.Enabled = false;
CheckAndAddВookingData.Enabled = false;
}
private void moveToLivingsButton_Click(object sender, EventArgs e)
{
dataGridView2.DataSource = RequestsSQLT.SelectAllFromLivingByCustomerId(conn, idOfChosenRow1);
ElementsSettings.SetDefaultSettingsToDGV(dataGridView2);
ElementsSettings.HideFirstXColumns(dataGridView2, 2);
ElementsSettings.HideLastXColumns(dataGridView2, 2);
tabControl1.SelectedIndex = 1;
}
private void checkAddititonalServicesButton_Click(object sender, EventArgs e)
{
valuesOfAdditionalServicesCell = RequestsSQLT.SelectAllFromAdditionalServicesWhereIsSetLivingID(conn, idOfChosenRowAS);
EditingAdditionalServices eas = new EditingAdditionalServices();
eas.ShowDialog();
eas.Focus();
}
private void editImagesOfApartmentButton_Click(object sender, EventArgs e)
{
RequestsSQLT.CollectImagesOfNthNumber(conn, numberOfChosenRow, RequestsSQLT.photosList);
ViewingApartmentsPhotos vap = new ViewingApartmentsPhotos();
vap.ShowDialog();
vap.Focus();
}
public void CheckAndAddВookingData_Click(object sender, EventArgs e) //Регистрация бронирования
{
if (textBox13.Text != "" && textBox14.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "")
{
SqlCommand command1 = new SqlCommand();
SqlCommand command2 = new SqlCommand();
Form1.OpenConnectionCorrect(Form1.conn);
int idOfCurrentCustomer = 0;
if (ElementsSettings.RowOfPassportSeriesConsistEnoughNumbers(textBox1) == true && ElementsSettings.RowOfPassportNumberConsistEnoughNumbers(textBox2) == true && ElementsSettings.ValuesOfGuestsAndKidsAreCorrect(textBox13, textBox14) == true && ElementsSettings.SettlingDateIsLessThenEvictionDate(ancSettlingDateTimePicker, ancEvictionDateTimePicker) == true) //Проверяем все ограничения ввода
{
//SqlCommand command = new SqlCommand("INSERT INTO Customer (passport_series, passport_number, name, surname, patronymic, birthday, tel_number) values('"+textBox1.Text+"', '"+textBox2.Text+"', '"+textBox3.Text+"', '"+textBox4.Text+"', '"+textBox5.Text+"', '"+textBox6.Text+"', '"+textBox7.Text+"')", Form1.conn);
if (RequestsSQLT.SelectNthIdFromCustomerWherePassportDataDefinedToString(Form1.conn, Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)) != "null") //Если клиент уже был постояльцем, то реистрируем проживание по его ID
{
idOfCurrentCustomer = Convert.ToInt32(RequestsSQLT.SelectNthIdFromCustomerWherePassportDataDefinedToString(Form1.conn, Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)));
if (RequestsSQLT.ValueOfCustomerLivingsAndBookingsForToday(Form1.conn, idOfCurrentCustomer) < 5)
{
command2 = new SqlCommand("INSERT INTO Booking (number, settling, eviction, value_of_guests, value_of_kids, customer_id) VALUES (@number, @settling, @eviction, @value_of_guests, @value_of_kids, @customer_id)", Form1.conn);
command2.Parameters.AddWithValue("@number", selectedNumberOfApartments);
command2.Parameters.AddWithValue("@settling", ancSettlingDateTimePicker.Value);
command2.Parameters.AddWithValue("@eviction", ancEvictionDateTimePicker.Value);
command2.Parameters.AddWithValue("@value_of_guests", textBox13.Text);
command2.Parameters.AddWithValue("@value_of_kids", textBox14.Text);
command2.Parameters.AddWithValue("@customer_id", idOfCurrentCustomer);
command2.ExecuteNonQuery();
//Тут можно потом поставить MessageBox, предупреждающий о том, что ткоей клинет уже есть в базе данных
}
}
else //Если клиент ещё не был постояльцем в отеле, то добавляем его запись в таблицу клиентов и сразу регистрируем новое проживание
{
command1 = new SqlCommand("INSERT INTO Customer (passport_series, passport_number, name, surname, patronymic, birthday, tel_number) VALUES (@passport_series, @passport_number, @name, @surname, @patronymic, @birthday, @tel_number)", Form1.conn);
command1.Parameters.AddWithValue("@passport_series", textBox1.Text);
command1.Parameters.AddWithValue("@passport_number", textBox2.Text);
command1.Parameters.AddWithValue("@name", textBox3.Text);
command1.Parameters.AddWithValue("@surname", textBox4.Text);
command1.Parameters.AddWithValue("@patronymic", textBox5.Text);
command1.Parameters.AddWithValue("@birthday", ancBirthdayDateTimePicker.Value);
command1.Parameters.AddWithValue("@tel_number", textBox7.Text);
command1.ExecuteNonQuery();
string check = RequestsSQLT.SelectNthIdFromCustomerWherePassportDataDefinedToString(Form1.conn, Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text));
idOfCurrentCustomer = Convert.ToInt32(RequestsSQLT.SelectNthIdFromCustomerWherePassportDataDefinedToString(Form1.conn, Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)));//Заного определяем ID только что добавленного клиента
command2 = new SqlCommand("INSERT INTO Booking (number, settling, eviction, value_of_guests, value_of_kids, customer_id) VALUES (@number, @settling, @eviction, @value_of_guests, @value_of_kids, @customer_id)", Form1.conn);
command2.Parameters.AddWithValue("@number", selectedNumberOfApartments);
command2.Parameters.AddWithValue("@settling", ancSettlingDateTimePicker.Value);
command2.Parameters.AddWithValue("@eviction", ancEvictionDateTimePicker);
command2.Parameters.AddWithValue("@value_of_guests", textBox13.Text);
command2.Parameters.AddWithValue("@value_of_kids", textBox14.Text);
command2.Parameters.AddWithValue("@customer_id", idOfCurrentCustomer);
command2.ExecuteNonQuery();
}
}
}
else
{
MessageBox.Show("Поля \"Имя\", \"Фамилия\", \"Отчество\", \"Количество гостей\", \"Количество детей\" обязательны к заполению");
}
}
public void Refresh3()
{
OpenConnectionCorrect(conn);
dataGridView3.DataSource = RequestsSQLT.SelectAllFromBooking(conn);
ElementsSettings.SetDefaultSettingsToDGV(dataGridView3);
ElementsSettings.HideFirstXColumns(dataGridView3, 2);
ElementsSettings.HideLastXColumns(dataGridView3, 2);
ElementsSettings.RenameLivingOrBookingDGV(dataGridView3);
}
private void dataGridView3_CellClick(object sender, DataGridViewCellEventArgs e)
{
SetButtonsActive2();
try
{
bookingS = ElementsSettings.SetDataFromLivingOrBookingDGVToTextBoxes(dataGridView3, e);
idOfChosenRow3 = Convert.ToInt32(RequestsSQLT.SelectNthIdFromCustomerWherePassportDataDefinedToString(conn, Convert.ToInt32(bookingS[0]), Convert.ToInt32(bookingS[1])));
textBox17.Text = bookingS[5];
textBox16.Text = bookingS[6];
if (bookingS[7] != "")
{
idOfChosenRowBooking = Convert.ToInt32(bookingS[8]);
}
} catch { }
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
SetButtonsActive0();
try
{
string[] s = ElementsSettings.SetDataFromCustomersDGVToTextBoxes(dataGridView1, e);
idOfChosenRow1 = Convert.ToInt32(RequestsSQLT.SelectNthIdFromCustomerWherePassportDataDefinedToString(conn, Convert.ToInt32(s[0]), Convert.ToInt32(s[1])));
textBox2.Text = s[0];
textBox3.Text = s[1];
textBox4.Text = s[2];
textBox5.Text = s[3];
textBox6.Text = s[4];
dateTimePicker1.Value = Convert.ToDateTime(s[5]);
//textBox7.Text = s[6];
textBox8.Text = s[6];
} catch { }
}
private void button1_Click(object sender, EventArgs e)
{
if (RequestsSQLT.IsNotThereSetNumberInApartments(Form1.conn, Convert.ToInt32(textBox1.Text)) == true)
{
if (textBox1.Text != "" && comboBox1.Text != "" && textBox3.Text != "")
{
if (RequestsSQLT.TypeOfApartmentsIsCorrect(comboBox1, Form1.conn) == true)
{
//SqlCommand command0 = new SqlCommand();
//command0 = new SqlCommand($"SET IDENTITY_INSERT Apartments ON", Form1.conn);
//command0.ExecuteNonQuery();
SqlCommand command1 = new SqlCommand();
command1 = new SqlCommand($"INSERT INTO Apartments (number, \"type\", price) VALUES (@number, @type, @price)", Form1.conn);
command1.Parameters.AddWithValue("@number", Convert.ToInt32(textBox1.Text));
command1.Parameters.AddWithValue("@type", comboBox1.Text);
command1.Parameters.AddWithValue("@price", Convert.ToInt32(textBox3.Text));
command1.ExecuteNonQuery();
//command0 = new SqlCommand($"SET IDENTITY_INSERT Apartments OFF", Form1.conn);
//command0.ExecuteNonQuery();
foreach (string i in selectedPhotos)
{
SqlCommand command2 = new SqlCommand();
command2 = new SqlCommand($"INSERT INTO Photos (path, number) VALUES (@path, @number)", Form1.conn);
command2.Parameters.AddWithValue("@path", i);
command2.Parameters.AddWithValue("@number", Convert.ToInt32(textBox1.Text));
command2.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Введён некорректный тип номера");
}
}
else
{
MessageBox.Show("Следует заполнить все поля");
}
}
else
{
MessageBox.Show("Номер с таким числовым обозначением уже есть в базе данных");
}
}
private void changeDiscountButton_Click(object sender, EventArgs e)
{
var dResult = MessageBox.Show("Вы уверены, что хотите обновить значение скидки?", "", MessageBoxButtons.YesNo);
if (dResult == DialogResult.Yes)
{
if (ElementsSettings.NewDiscountIsInPercentage(newDiscountTextBox) == true)
{
RequestsSQLT.SetNewDiscount(conn, Convert.ToInt32(newDiscountTextBox.Text));
}
else
{
MessageBox.Show("Введено недопустимое значение.");
}
}
newDiscountTextBox.Clear();
discount = RequestsSQLT.GetCurrentDiscount(conn);
label19.Text = discount.ToString() + " %";
}
private void dataGridView2_CellClick(object sender, DataGridViewCellEventArgs e)
{
SetButtonsActive1();
try
{
string[] s = ElementsSettings.SetDataFromLivingOrBookingDGVToTextBoxes(dataGridView2, e);
idOfChosenRow2 = Convert.ToInt32(RequestsSQLT.SelectNthIdFromCustomerWherePassportDataDefinedToString(conn, Convert.ToInt32(s[0]), Convert.ToInt32(s[1])));
textBox12.Text = s[5];
textBox11.Text = s[6];
if (s[7] != "")
{
idOfChosenRowAS = Convert.ToInt32(s[7]);
}
if (s[8] != "")
{
idOfChosenRowLiving = Convert.ToInt32(s[8]);
}
} catch { }
}
private void deleteApartmentsButton_Click(object sender, EventArgs e)
{
var dResult = MessageBox.Show("Вы уверены, что хотите удалить эту запись?", "", MessageBoxButtons.YesNo);
if (dResult == DialogResult.Yes)
{
if (RequestsSQLT.NumberOfApartmentsIsFree(conn, numberOfChosenRow) == true) //Проверяем все ограничения ввода для таблицы клиентов
{
SqlCommand command1 = new SqlCommand();
command1 = new SqlCommand($"DELETE FROM Apartments WHERE number = {Convert.ToInt32(numberTextBox.Text)}", conn);
command1.ExecuteNonQuery();
SqlCommand command2 = new SqlCommand();
command2 = new SqlCommand($"DELETE FROM Photos WHERE number = {Convert.ToInt32(numberTextBox.Text)}", conn);
command2.ExecuteNonQuery();
Refresh4();
}
else
{
MessageBox.Show("Редактирование данной записи недоступно. На номер зарегистрированно неоконченное проживание или оформлена бронь.");
}
}
}
private void deleteBookingDataButton_Click(object sender, EventArgs e)
{
var dResult = MessageBox.Show("Вы уверены, что хотите удалить эту запись?", "", MessageBoxButtons.YesNo);
if (dResult == DialogResult.Yes)
{
SqlCommand command = new SqlCommand();
command = new SqlCommand($"DELETE FROM Booking WHERE booking_id = {idOfChosenRowBooking}", conn);
command.ExecuteNonQuery();
Refresh3();
}
if (RequestsSQLT.DoesCustomerHasNoLivivngsAndBookings(conn, idOfChosenRow3) == true)
{
var dr = MessageBox.Show("У клиента не осталось ни проживаний, ни броней. Хотите удалить его из базы данных?", "", MessageBoxButtons.YesNo);
if (dr == DialogResult.Yes)
{
SqlCommand command0 = new SqlCommand();
command0 = new SqlCommand($"DELETE FROM Customer WHERE customer_id = {idOfChosenRow3}", conn);
command0.ExecuteNonQuery();
Refresh1();
}
}
}
private void moveToCustomerFromBookingButton_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = RequestsSQLT.SelectAllFromCustomerByCustomerId(conn, idOfChosenRow3);
tabControl1.SelectedIndex = 0;
}
