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 Inventory
{
    /// <summary>
    /// Логика взаимодействия для SystemAdminWindow.xaml
    /// </summary>
    public partial class SystemAdminWindow : Window
    {
        public SystemAdminWindow()
        {
            InitializeComponent();
        }

        SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Inventory;Integrated Security=True");

        public void time()
        {
            DispatcherTimer timer = new DispatcherTimer();
            timer.Tick += new EventHandler(Update_Timer_Tick);
            timer.Interval = new TimeSpan(0, 0, 1);
            timer.Start();
        }

        void privet()
        {
            string[] randoms = new string[5];
            randoms[0] = "Привет!";
            randoms[1] = "Как дела?";
            randoms[2] = "Давно не виделись!";
            randoms[3] = "Здравствуй!";
            randoms[4] = "Добро пожаловать!";
            Random r = new Random();
            string randsror = randoms[r.Next(0, randoms.Length - 1)];
            labelpriv.Content = randsror.ToString();
        }

        private void Update_Timer_Tick(object sender, EventArgs e)
        {
            timetxt.Text = DateTime.Now.ToString();
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            int index = int.Parse(((Button)e.Source).Uid);

            GridCursor.Margin = new Thickness(298, 120 + (50 * index), 0, 0);


            switch (index)
            {
                case 0:
                    gridglav.Visibility = Visibility.Visible;
                    gridoborot.Visibility = Visibility.Hidden;
                    gridkabinet.Visibility = Visibility.Hidden;
                    gridychet.Visibility = Visibility.Hidden;
                    GridCursor.Margin = new Thickness(298, 120 + (50 * 0), 0, 0);
                    privet();
                    break;
                case 1:
                    if (gridkabinet.Visibility == Visibility.Visible)
                    {
                        gridkabinet.Visibility = Visibility.Hidden;
                        gridoborot.Visibility = Visibility.Hidden;
                        gridglav.Visibility = Visibility.Visible;
                        privet();
                        GridCursor.Margin = new Thickness(298, 120 + (50 * 0), 0, 0);
                    }
                    else
                    {
                        gridkabinet.Visibility = Visibility.Visible;
                        gridychet.Visibility = Visibility.Hidden;
                        gridoborot.Visibility = Visibility.Hidden;
                        gridglav.Visibility = Visibility.Hidden;
                    }
                    break;
                case 2:             
                    if (gridoborot.Visibility == Visibility.Visible)
                    {
                        gridoborot.Visibility = Visibility.Hidden;
                        gridglav.Visibility = Visibility.Visible;
                        privet();
                        GridCursor.Margin = new Thickness(298, 120 + (50 * 0), 0, 0);
                    }
                    else
                    {
                        gridoborot.Visibility = Visibility.Visible;
                        gridychet.Visibility = Visibility.Hidden;
                        gridglav.Visibility = Visibility.Hidden;
                        gridkabinet.Visibility = Visibility.Hidden;
                    }
                    break;
                case 3:
                    if (gridychet.Visibility == Visibility.Visible)
                    {
                        gridychet.Visibility = Visibility.Hidden;
                        gridoborot.Visibility = Visibility.Hidden;
                        gridkabinet.Visibility = Visibility.Hidden;
                        gridglav.Visibility = Visibility.Visible;
                        privet();
                        GridCursor.Margin = new Thickness(298, 120 + (50 * 0), 0, 0);
                    }
                    else
                    {
                        gridychet.Visibility = Visibility.Visible;
                        gridoborot.Visibility = Visibility.Hidden;
                        gridglav.Visibility = Visibility.Hidden;
                        gridkabinet.Visibility = Visibility.Hidden;
                    }
                    break;
            }
        }

        private void Back(object sender, RoutedEventArgs e)
        {
            MainWindow mainWindow = new MainWindow();
            mainWindow.Show();
            this.Close();
        }

        private void Exit(object sender, RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }

        private void Minimized(object sender, RoutedEventArgs e)
        {
            this.WindowState = WindowState.Minimized;
        }

        private void Grid_MouseDown(object sender, MouseButtonEventArgs e)
        {
            try
            {
                DragMove();
            }
            catch
            {
            }
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            time();
            privet();
            showdatagridoborod();
            showdatagridkabinet();
            showdatagridrecord();
            fillcombocabinet();
            fillcombooboryd();
        }

        public void showdatagridoborod()
        {
            try
            {
                con.Open();
                string sql = "SELECT * FROM Equipment";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("Equipment");
                dataAdapter.Fill(data);
                dataoborod.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                dataoborod.Columns[1].Header = "Наименование оборудования";
                dataoborod.Columns[0].Visibility = Visibility.Collapsed;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        public void addoborod()
        {
            if (oborydname.Text == "")
            {
                MessageBox.Show("Заполните поле наименования оборудования!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "INSERT INTO Equipment (EquipmentName) VALUES('" + oborydname.Text + "')";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    refreshoborod();
                    fillcombooboryd();
                    MessageBox.Show("Оборудование добавлено!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void Addoborod(object sender, RoutedEventArgs e)
        {
            addoborod();
        }

        private void Updateoborod(object sender, RoutedEventArgs e)
        {
            if (idoborodtxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else if (oborydname.Text == "")
            {
                MessageBox.Show("Заполните поле наименования оборудования!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "Update Equipment set EquipmentName ='" + oborydname.Text + "' WHERE ID_Equipment = '" + idoborodtxt.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    refreshoborod();
                    fillcombooboryd();
                    MessageBox.Show("Наименование оборудования обновлено!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void Deleteoborod(object sender, RoutedEventArgs e)
        {
            if (idoborodtxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "DELETE FROM Equipment WHERE ID_Equipment = '" + idoborodtxt.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    refreshoborod();
                    fillcombooboryd();
                    MessageBox.Show("Оборудование удалено!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        public void refreshoborod()
        {
            poiskoboryd.Text = "";
            oborydname.Text = "";
            idoborodtxt.Text = "";
            showdatagridoborod();
        }

        private void Refreshoborod(object sender, RoutedEventArgs e)
        {
            refreshoborod();
        }

        private void dataoborod_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataGrid gd = (DataGrid)sender;
                DataRowView rowView = gd.SelectedItem as DataRowView;
                if (rowView != null)
                {
                    idoborodtxt.Text = rowView["ID_Equipment"].ToString();
                    oborydname.Text = rowView["EquipmentName"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void poiskoboryd_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (poiskoboryd.Text == "")
            {
                try
                {
                    con.Open();
                    string sql = "SELECT * FROM Equipment";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    DataTable data = new DataTable("Equipment");
                    dataAdapter.Fill(data);
                    dataoborod.ItemsSource = data.DefaultView;
                    dataAdapter.Update(data);
                    con.Close();
                    dataoborod.Columns[1].Header = "Наименование оборудования";
                    dataoborod.Columns[0].Visibility = Visibility.Collapsed;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
            else if (sender is TextBox textBox)
            {
                try
                {
                    textBox.Text = new string
                  (textBox.Text.Where(ch => (ch >= 'А' && ch <= 'Я') || (ch >= 'а' && ch <= 'я') || (ch >= ' ')).ToArray());
                    con.Open();
                    string sql = "SELECT * FROM Equipment WHERE EquipmentName Like '"+poiskoboryd.Text+"%'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    DataTable data = new DataTable("Equipment");
                    dataAdapter.Fill(data);
                    dataoborod.ItemsSource = data.DefaultView;
                    dataAdapter.Update(data);
                    con.Close();
                    dataoborod.Columns[1].Header = "Наименование оборудования";
                    dataoborod.Columns[0].Visibility = Visibility.Collapsed;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void Rus_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (sender is TextBox textBox)
            {
                textBox.Text = new string
                 (textBox.Text.Where(ch => (ch >= 'А' && ch <= 'Я') || (ch >= 'а' && ch <= 'я') || (ch >= ' ' && ch <= ' ') || (ch >= '0' && ch <= '9') || (ch >= '№' && ch <= '№')).ToArray());
            }
        }

        private void oborydname_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Enter)
            {
                addoborod();
            }    
        }

        public void showdatagridkabinet()
        {
            try
            {
                con.Open();
                string sql = "SELECT * FROM Cabinet";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("Cabinet");
                dataAdapter.Fill(data);
                datakabinet.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                datakabinet.Columns[1].Header = "Наименование кабинета";
                datakabinet.Columns[0].Visibility = Visibility.Collapsed;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        public void addKabinet()
        {
            if (kabinetname.Text == "")
            {
                MessageBox.Show("Заполните поле наименования оборудования!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "INSERT INTO Cabinet (CabinetName) VALUES('" + kabinetname.Text + "')";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    refreskabinet();
                    fillcombocabinet();
                    MessageBox.Show("Кабинет добавлен!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void poiskabinet_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (poiskabinet.Text == "")
            {
                try
                {
                    con.Open();
                    string sql = "SELECT * FROM Cabinet";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    DataTable data = new DataTable("Cabinet");
                    dataAdapter.Fill(data);
                    datakabinet.ItemsSource = data.DefaultView;
                    dataAdapter.Update(data);
                    con.Close();
                    datakabinet.Columns[1].Header = "Наименование кабинета";
                    datakabinet.Columns[0].Visibility = Visibility.Collapsed;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
            else if (sender is TextBox textBox)
            {
                try
                {
                    textBox.Text = new string
                  (textBox.Text.Where(ch => (ch >= 'А' && ch <= 'Я') || (ch >= 'а' && ch <= 'я') || (ch >= ' ')).ToArray());
                    con.Open();
                    string sql = "SELECT * FROM Cabinet WHERE CabinetName Like '" + poiskabinet.Text + "%'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    DataTable data = new DataTable("Cabinet");
                    dataAdapter.Fill(data);
                    datakabinet.ItemsSource = data.DefaultView;
                    dataAdapter.Update(data);
                    con.Close();
                    datakabinet.Columns[1].Header = "Наименование кабинета";
                    datakabinet.Columns[0].Visibility = Visibility.Collapsed;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void kabinetname_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Enter)
            {
                addKabinet();
            }
        }

        private void RefreshKabinet(object sender, RoutedEventArgs e)
        {
            refreskabinet();
        }

        public void refreskabinet()
        {
            poiskabinet.Text = "";
            kabinetname.Text = "";
            idkabinettxt.Text = "";
            showdatagridkabinet();
        }

        private void AddKabinet_Click(object sender, RoutedEventArgs e)
        {
            addKabinet();
        }

        private void UpdateKabinet_Click(object sender, RoutedEventArgs e)
        {
            if (idkabinettxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else if (kabinetname.Text == "")
            {
                MessageBox.Show("Заполните поле наименования кабинета!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "Update Cabinet set CabinetName ='" + kabinetname.Text + "' WHERE ID_Cabinet = '" + idkabinettxt.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    refreskabinet();
                    fillcombocabinet();
                    MessageBox.Show("Наименование кабинета обновлено!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void DeleteKabinet_Click(object sender, RoutedEventArgs e)
        {
            if (idkabinettxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "DELETE FROM Cabinet WHERE ID_Cabinet = '" + idkabinettxt.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    refreskabinet();
                    fillcombocabinet();
                    MessageBox.Show("Кабинет удален!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void datakabinet_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataGrid gd = (DataGrid)sender;
                DataRowView rowView = gd.SelectedItem as DataRowView;
                if (rowView != null)
                {
                    idkabinettxt.Text = rowView["ID_Cabinet"].ToString();
                    kabinetname.Text = rowView["CabinetName"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }


        public void fillcombocabinet()
        {
            try
            {
                combocabinet.Items.Clear();
                con.Open();
                SqlCommand sql = con.CreateCommand();
                sql.CommandType = CommandType.Text;
                sql.CommandText = "Select CabinetName from Cabinet";
                sql.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sql);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    combocabinet.Items.Add(dr["CabinetName"].ToString());
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        public void fillcombooboryd()
        {
            try
            {
                combooboryd.Items.Clear();
                con.Open();
                SqlCommand sql = con.CreateCommand();
                sql.CommandType = CommandType.Text;
                sql.CommandText = "Select EquipmentName from Equipment";
                sql.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sql);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    combooboryd.Items.Add(dr["EquipmentName"].ToString());
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        public void showdatagridrecord()
        {
            try
            {
                con.Open();
                string sql = "SELECT ID_Record,Cabinet.CabinetName,Equipment.EquipmentName,Record.Quantity FROM Record inner join Cabinet on Record.ID_Cabinet = Cabinet.ID_Cabinet inner join Equipment on Record.ID_Equipment = Equipment.ID_Equipment";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("Record");
                dataAdapter.Fill(data);
                dataychet.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                dataychet.Columns[1].Header = "Наименование кабинета";
                dataychet.Columns[2].Header = "Наименование оборудования";
                dataychet.Columns[3].Header = "Количество";
                dataychet.Columns[0].Visibility = Visibility.Collapsed;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка с подключением к БД! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
    }
}