Otel/HotelCalifornia/Rezerv.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Threading;

namespace HotelCalifornia
{
    /// <summary>
    /// Логика взаимодействия для Rezerv.xaml
    /// </summary>
    public partial class Rezerv : Window
    {
        public Rezerv()
        {
            InitializeComponent();
            //Таймер на обновление времени
            DispatcherTimer timer = new DispatcherTimer();
            timer.Tick += new EventHandler(Update_Timer_Tick);
            timer.Interval = new TimeSpan(0, 0, 1);
            timer.Start();
            //Начало даты
            datein.DisplayDateStart = DateTime.Now;
            dateout.DisplayDateStart = DateTime.Now;
        }

        //Строка подключения
        SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=kursah;Integrated Security=True");

        //Вывод даты и время в textblock
        private void Update_Timer_Tick(object sender, EventArgs e)
        {
            timetxt.Text = DateTime.Now.ToString();
        }

        //Перетаскивание окна
        private void Grid_MouseDown(object sender, MouseButtonEventArgs e)
        {
            try
            {
                DragMove();
            }
            catch
            {
            }
        }

        //Возврат к окну выбора функции
        private void Back(object sender, RoutedEventArgs e)
        {
            Variant variant = new Variant();
            variant.idadmintxt.Text = idadmintxt.Text;
            this.Close();
            variant.Show();
        }

        //Свернуть окно
        private void WindMin_Click(object sender, RoutedEventArgs e)
        {
             this.WindowState = WindowState.Minimized;
        }

        //Выход из приложения
        private void Close(object sender, RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }

        //Выбор строки из БД
        private void datarezerv_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataGrid gd = (DataGrid)sender;
                DataRowView rowView = gd.SelectedItem as DataRowView;
                if (rowView != null)
                {
                    numbroomtxt.Text = rowView["Number_Room"].ToString();
                    datein.Text = rowView["Date_in"].ToString();
                    dateout.Text = rowView["Date_out"].ToString();
                    clientcombo.Text = rowView["LastName_Client"].ToString();
                    roomcombo.Text = rowView["Number_Room"].ToString();
                    roomcombo.IsEnabled = false;
                    clientcombo.IsEnabled = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        //Запуск в самом начале
        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            fillcomboroom();
            fillcomboclient();
            fill1comboroom();
            showgrid();
        }

        //Заполнение combo
        #region Combo
        void fillcomboroom()
        {
            try
            {
                roomcombo.Items.Clear();
                con.Open();
                SqlCommand sql = con.CreateCommand();
                sql.CommandType = CommandType.Text;
                sql.CommandText = "Select Number_Room from Room WHERE Status_Room = 1";
                sql.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sql);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    roomcombo.Items.Add(dr["Number_Room"].ToString());
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        void fill1comboroom()
        {
            try
            {
                room1combo.Items.Clear();
                con.Open();
                SqlCommand sql = con.CreateCommand();
                sql.CommandType = CommandType.Text;
                sql.CommandText = "Select Number_Room from Room WHERE Status_Room = 2";
                sql.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sql);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    room1combo.Items.Add(dr["Number_Room"].ToString());
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        void fillcomboclient()
        {
            try
            {
                clientcombo.Items.Clear();
                con.Open();
                SqlCommand sql = con.CreateCommand();
                sql.CommandType = CommandType.Text;
                sql.CommandText = "Select LastName_Client from Client";
                sql.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sql);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    clientcombo.Items.Add(dr["LastName_Client"].ToString());
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
        #endregion

        //Добавление резервирования
        private void Add_Click(object sender, RoutedEventArgs e)
        {
            if (clientcombo.Text == "" || roomcombo.Text == "" || dateout.SelectedDate == null || datein.SelectedDate == null)
            {
                MessageBox.Show("Заполните все поля!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else if (datein.SelectedDate >= dateout.SelectedDate)
            {
                MessageBox.Show("Дата указана неверно!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Select * from Client where LastName_Client = '" + clientcombo.Text + "'", con);
                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        string stridclient = dataSet.Tables[0].Rows[0]["ID_Client"].ToString();
                        SqlCommand cmd2 = new SqlCommand("Select * from Rezervirovanie where ID_Client = '" + stridclient.ToString() + "' and  ID_Status = '1'; Select * from RoomClient where ID_Client = '" + stridclient.ToString() + "'", con);
                        cmd2.CommandType = CommandType.Text;
                        SqlDataAdapter adapter2 = new SqlDataAdapter();
                        adapter2.SelectCommand = cmd2;
                        DataSet dataSet2 = new DataSet();
                        adapter2.Fill(dataSet2);
                        if (dataSet2.Tables[0].Rows.Count > 0)
                        {
                            con.Close();
                            MessageBox.Show("Клиент уже забронировал комнату!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
                        }
                        else if (dataSet2.Tables[1].Rows.Count > 0)
                        {
                            con.Close();
                            MessageBox.Show("Клиент уже заселён!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
                        }
                        else
                        {
                            SqlCommand cmd1 = new SqlCommand("Select ID_Room from Room where Number_Room = '" + roomcombo.Text + "'; Select ID_Client from Client where LastName_Client = '" + clientcombo.Text + "'", con);
                            cmd1.CommandType = CommandType.Text;
                            SqlDataAdapter adapter1 = new SqlDataAdapter();
                            adapter1.SelectCommand = cmd1;
                            DataSet dataSet1 = new DataSet();
                            adapter1.Fill(dataSet1);
                            if (dataSet1.Tables[0].Rows.Count > 0)
                            {
                                string stridroom = dataSet1.Tables[0].Rows[0]["ID_Room"].ToString();
                                string stridclient1 = dataSet1.Tables[1].Rows[0]["ID_Client"].ToString();
                                string stridstatus = "1";
                                string sql = "INSERT INTO Rezervirovanie (ID_Room,ID_Client,Date_in,Date_out,ID_Administrator, ID_Status)  VALUES('" + stridroom.ToString() + "','" + stridclient1.ToString() + "','" + datein.SelectedDate + "','" + dateout.SelectedDate + "','" + idadmintxt.Text.ToString() + "', '"+stridstatus.ToString()+"'); Update Room set Status_Room ='" + 2 + "' Where ID_Room = '" + stridroom.ToString() + "'; INSERT INTO RoomClient (ID_Room,ID_Client) VALUES('" + stridroom.ToString() + "','" + stridclient1.ToString() + "')";
                                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                                dataAdapter.SelectCommand.ExecuteNonQuery();
                                con.Close();
                                MessageBox.Show("Бронирование успешно создано!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
                            }
                        }
                    }
                    fillcomboroom();
                    fill1comboroom();
                    roomcombo.Text = "";
                    clientcombo.Text = "";
                    datein.SelectedDate = null;
                    dateout.SelectedDate = null;
                    showgrid();
                }             
            
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        //Обновление резервирования
        private void Update_Click(object sender, RoutedEventArgs e)
        {
            if (numbroomtxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else if (datein.SelectedDate == null || dateout.SelectedDate == null)
            {
                MessageBox.Show("Заполните все поля!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else if (datein.SelectedDate >= dateout.SelectedDate)
            {
                MessageBox.Show("Дата указана неверно!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd1 = new SqlCommand("Select ID_Room from Room where Number_Room = '" + numbroomtxt.Text + "'", con);
                    cmd1.CommandType = CommandType.Text;
                    SqlDataAdapter adapter1 = new SqlDataAdapter();
                    adapter1.SelectCommand = cmd1;
                    DataSet dataSet1 = new DataSet();
                    adapter1.Fill(dataSet1);
                    if (dataSet1.Tables[0].Rows.Count > 0)
                    {
                        string stridroom = dataSet1.Tables[0].Rows[0]["ID_Room"].ToString();
                        string stridstatus = "1";
                        string sql = "Update Rezervirovanie set Date_in ='" + datein.SelectedDate + "', Date_out = '" + dateout.SelectedDate + "', ID_Administrator = '"+idadmintxt.Text+"', ID_Status = '"+stridstatus.ToString()+"' where ID_Room = '" + stridroom.ToString() + "'";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        showgrid();
                        roomcombo.Text = "";
                        room1combo.Text = "";
                        datein.SelectedDate = null;
                        dateout.SelectedDate = null;
                        roomcombo.IsEnabled = true;
                        clientcombo.IsEnabled = true;
                        clientcombo.Text = "";
                        numbroomtxt.Text = "";
                        MessageBox.Show("Дата резервироания изменена!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                    }                    
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        //Удаление резервирования
        private void Delete_Click(object sender, RoutedEventArgs e)
        {
            if (numbroomtxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd1 = new SqlCommand("Select ID_Room from Room where Number_Room = '" + numbroomtxt.Text + "'; Select ID_Client From Client where LastName_Client = '" + clientcombo.Text + "'", con);
                    cmd1.CommandType = CommandType.Text;
                    SqlDataAdapter adapter1 = new SqlDataAdapter();
                    adapter1.SelectCommand = cmd1;
                    DataSet dataSet1 = new DataSet();
                    adapter1.Fill(dataSet1);
                    if (dataSet1.Tables[0].Rows.Count > 0)
                    {
                        string stridroom = dataSet1.Tables[0].Rows[0]["ID_Room"].ToString();
                        string stridclient = dataSet1.Tables[1].Rows[0]["ID_Client"].ToString();
                        string stridstatus = "2";
                        string sql = "Update Rezervirovanie set ID_Status = '" + stridstatus.ToString()+"' WHERE ID_Room = '" + stridroom.ToString() + "'; Update Room set Status_Room ='" + 1 + "' Where ID_Room = '" + stridroom.ToString() + "'; DELETE FROM RoomClient WHERE ID_Room = '" + stridroom.ToString() + "';";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        showgrid();
                        fillcomboroom();
                        fill1comboroom();
                        roomcombo.Text = "";
                        room1combo.Text = "";
                        datein.SelectedDate = null;
                        dateout.SelectedDate = null;
                        roomcombo.IsEnabled = true;
                        clientcombo.IsEnabled = true;
                        clientcombo.Text = "";
                        numbroomtxt.Text = "";
                        MessageBox.Show("Резервирование удалено!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                    }
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        //Фомировка данных из БД
        void showgrid()
        {
            try
            {
                con.Open();
                string sql = "SELECT Number_Room, Client.LastName_Client, Rezervirovanie.Date_in,Rezervirovanie.Date_out From Room inner join Rezervirovanie on Room.ID_Room = Rezervirovanie.ID_Room inner join Client on Rezervirovanie.[ID_Client] = Client.ID_Client group by Number_Room, Client.LastName_Client, Rezervirovanie.Date_in,Rezervirovanie.Date_out,Rezervirovanie.ID_Status having Rezervirovanie.ID_Status = '1'";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("Rezervirovanie");
                dataAdapter.Fill(data);
                datarezerv.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                datarezerv.Columns[0].Header = "Номер комнаты";
                datarezerv.Columns[1].Header = "Фамилия";
                datarezerv.Columns[2].Header = "Дата прибытия";
                datarezerv.Columns[3].Header = "Дата отбытия";
                (datarezerv.Columns[2] as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";
                (datarezerv.Columns[3] as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        //Поиск
        private void Search_Click(object sender, RoutedEventArgs e)
        {
            if (room1combo.Text == "")
            {
                MessageBox.Show("Выберите комнату для поиска!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "SELECT Number_Room, Client.LastName_Client, Rezervirovanie.Date_in,Rezervirovanie.Date_out From Room inner join Rezervirovanie on Room.ID_Room = Rezervirovanie.ID_Room inner join Client on Rezervirovanie.[ID_Client] = Client.ID_Client group by Number_Room, Client.LastName_Client, Rezervirovanie.Date_in,Rezervirovanie.Date_out having Number_Room = '"+room1combo.Text+"'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    DataTable data = new DataTable("Rezervirovanie");
                    dataAdapter.Fill(data);
                    datarezerv.ItemsSource = data.DefaultView;
                    dataAdapter.Update(data);
                    con.Close();
                    datarezerv.Columns[0].Header = "Номер комнаты";
                    datarezerv.Columns[1].Header = "Фамилия";
                    datarezerv.Columns[2].Header = "Дата прибытия";
                    datarezerv.Columns[3].Header = "Дата отбытия";
                    (datarezerv.Columns[2] as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";
                    (datarezerv.Columns[3] as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        //Обновление
        private void Refresh_Click(object sender, RoutedEventArgs e)
        {
            showgrid();
            roomcombo.Text = "";
            room1combo.Text = "";
            datein.SelectedDate = null;
            dateout.SelectedDate = null;
            roomcombo.IsEnabled = true;
            numbroomtxt.Text = "";
            clientcombo.IsEnabled = true;
            clientcombo.Text = "";
        }
    }
}