Otel/HotelCalifornia/ClientRoom.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.Windows.Threading;
using System.Data;
using System.Data.SqlClient;

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

        //Строка подключения
        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 Close(object sender, RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }

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

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

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

        //Добавление заселения
        private void Add_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (clientcombo.Text == "" || roomcombo.Text == "")
                {
                    MessageBox.Show("Заполните все поля!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                else
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Select * from Room where Number_Room ='" + roomcombo.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 idroom = dataSet.Tables[0].Rows[0]["ID_Room"].ToString();
                        SqlCommand cmd1 = new SqlCommand("Select * 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 idclient = dataSet1.Tables[0].Rows[0]["ID_Client"].ToString();
                            SqlCommand cmd2 = new SqlCommand("Select * from RoomClient where ID_Client = '"+ idclient.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
                            {
                                string sql = "INSERT INTO RoomClient (ID_Room,ID_Client) VALUES('" + idroom.ToString() + "','" + idclient.ToString() + "')";
                                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                                dataAdapter.SelectCommand.ExecuteNonQuery();
                                con.Close();
                                showgrid();
                                roomcombo.Text = "";
                                clientcombo.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 (roomcombo.Text == "" || clientcombo.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Select * from Room where Number_Room ='" + roomcombo.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 idroom = dataSet.Tables[0].Rows[0]["ID_Room"].ToString();
                        SqlCommand cmd1 = new SqlCommand("Select * 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 idclient = dataSet1.Tables[0].Rows[0]["ID_Client"].ToString();
                            SqlCommand cmd2 = new SqlCommand("Select * from RoomClient where ID_Client = '" + idclient.ToString() + "' and ID_Room = '" + idroom.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)
                            {
                                string sql = "DELETE FROM RoomClient WHERE ID_Room = '" + idroom.ToString() + "' and ID_Client = '"+idclient.ToString()+"'; Update Rezervirovanie set ID_Status = '2' where ID_Client = '"+idclient.ToString()+"'; Update Room set Status_Room = '1' Where ID_Room = '"+idroom.ToString()+"'";
                                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                                dataAdapter.SelectCommand.ExecuteNonQuery();
                                con.Close();
                                showgrid();
                                roomcombo.Text = "";
                                clientcombo.Text = "";
                                MessageBox.Show("Запись удалена!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);                   
                            }
                            else
                            {
                                con.Close();
                                roomcombo.Text = "";
                                clientcombo.Text = "";
                                MessageBox.Show("Такой записи нет!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

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

        //Фомировка данных из БД
        void showgrid()
        {
            try
            {
                con.Open();
                string sql = "SELECT Number_Room, Client.LastName_Client From Room inner join RoomClient on Room.ID_Room = RoomClient.ID_Room inner join Client on RoomClient.ID_Client = Client.ID_Client";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("RoomClient");
                dataAdapter.Fill(data);
                dataclientroom.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                dataclientroom.Columns[0].Header = "Номер комнты";
                dataclientroom.Columns[1].Header = "Фамилия клиента";
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        //Заполнение combo
        #region Combo
        //Combo комнаты
        void fillroomcombo()
        {
            try
            {
                roomcombo.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)
                {
                    roomcombo.Items.Add(dr["Number_Room"].ToString());
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
        //Combo клиента
        void fillclientcombo()
        {
            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 Refresh_Click(object sender, RoutedEventArgs e)
        {
            showgrid();
            roomcombo.Text = "";
            clientcombo.Text = "";
        }
    }
}