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

namespace SkladProject
{
    /// <summary>
    /// Логика взаимодействия для WorkerWindow.xaml
    /// </summary>
    public partial class WorkerWindow : Window
    {
        public WorkerWindow()
        {
            InitializeComponent();
            gridprivet.Visibility = Visibility.Visible;
            DispatcherTimer timer = new DispatcherTimer();
            timer.Tick += new EventHandler(Update_Timer_Tick);
            timer.Interval = new TimeSpan(0, 0, 1);
            timer.Start();
            dateproduct.DisplayDateStart = DateTime.Now;
        }

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

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

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

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

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

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

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            fillcomboproduct();
            filllichcab();
            showdataproduct();
            showdocument();
            showtraffic();
            fillvsego();
        }

        private void UserInform(object sender, RoutedEventArgs e)
        {
            if (gridlich.Visibility == Visibility.Visible)
            {
                gridlich.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Visible;
            }
            else
            {
                gridlich.Visibility = Visibility.Visible;
                gridproduct.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Hidden;
                gridskladinfo.Visibility = Visibility.Hidden;
                griddocument.Visibility = Visibility.Hidden;
                gridtraffic.Visibility = Visibility.Hidden;
            }
        }

        private void Production(object sender, RoutedEventArgs e)
        {
            if (gridproduct.Visibility == Visibility.Visible)
            {
                gridproduct.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Visible;
            }
            else
            {
                gridproduct.Visibility = Visibility.Visible;
                gridlich.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Hidden;
                gridskladinfo.Visibility = Visibility.Hidden;
                griddocument.Visibility = Visibility.Hidden;
                gridtraffic.Visibility = Visibility.Hidden;
            }
        }

        private void Document(object sender, RoutedEventArgs e)
        {
            if (griddocument.Visibility == Visibility.Visible)
            {
                griddocument.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Visible;
            }
            else
            {
                griddocument.Visibility = Visibility.Visible;
                gridproduct.Visibility = Visibility.Hidden;
                gridlich.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Hidden;
                gridskladinfo.Visibility = Visibility.Hidden;
                gridtraffic.Visibility = Visibility.Hidden;
            }
        }

        private void Traffic(object sender, RoutedEventArgs e)
        {
            if (gridtraffic.Visibility == Visibility.Visible)
            {
                gridtraffic.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Visible;
            }
            else
            {
                gridtraffic.Visibility = Visibility.Visible;
                griddocument.Visibility = Visibility.Hidden;
                gridproduct.Visibility = Visibility.Hidden;
                gridlich.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Hidden;
                gridskladinfo.Visibility = Visibility.Hidden;
            }
        }

        private void SkladInfo(object sender, RoutedEventArgs e)
        {
            if (gridskladinfo.Visibility == Visibility.Visible)
            {
                gridskladinfo.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Visible;
            }
            else
            {
                gridskladinfo.Visibility = Visibility.Visible;
                gridproduct.Visibility = Visibility.Hidden;
                gridlich.Visibility = Visibility.Hidden;
                gridprivet.Visibility = Visibility.Hidden;
                griddocument.Visibility = Visibility.Hidden;
                gridtraffic.Visibility = Visibility.Hidden;
            }
        }

        void fillvsego()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT SUM(Quantity) as 'Sum' FROM Documents", 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)
                {
                    vsegotxt.Text = "Количество продукции на складе: " + dataSet.Tables[0].Rows[0]["Sum"].ToString();
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        void filllichcab()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("Select * from Users where ID_User ='" + idusertxt.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)
                {
                    firstnametxt.Text = "Имя: " + dataSet.Tables[0].Rows[0]["FirstName"].ToString();
                    lastnametxt.Text = "Фамилия: " + dataSet.Tables[0].Rows[0]["LastName"].ToString();
                    middlename.Text = "Отчество: " + dataSet.Tables[0].Rows[0]["MiddleName"].ToString();
                    logintxt.Text = "Логин: " + dataSet.Tables[0].Rows[0]["Login"].ToString();
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        void showdataproduct()
        {
            try
            {
                con.Open();
                string sql = "SELECT ID_Product,NameProduct,DateProduct From Production";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("Production");
                dataAdapter.Fill(data);
                dataproduct.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                dataproduct.Columns[0].Header = "ID";
                dataproduct.Columns[1].Header = "Название продукта";
                dataproduct.Columns[2].Header = "Срок хранения";
                dataproduct.Columns[0].Visibility = Visibility.Collapsed;
                (dataproduct.Columns[2] as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void dataproduct_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataGrid gd = (DataGrid)sender;
                DataRowView rowView = gd.SelectedItem as DataRowView;
                if (rowView != null)
                {

                    idproducttxt.Text = rowView["ID_Product"].ToString();
                    productnametxt.Text = rowView["NameProduct"].ToString();
                    dateproduct.Text = rowView["DateProduct"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

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

        private void Refresh_Click(object sender, RoutedEventArgs e)
        {
            idproducttxt.Text = "";
            productnametxt.Text = "";
            dateproduct.SelectedDate = null;
            showdataproduct();
        }

        private void Add_Click(object sender, RoutedEventArgs e)
        {
            if (productnametxt.Text == "" || dateproduct.SelectedDate == null)
            {
                MessageBox.Show("Заполните все поля!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "INSERT INTO Production (NameProduct,DateProduct) VALUES('" + productnametxt.Text + "','" + dateproduct.SelectedDate + "')";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    idproducttxt.Text = "";
                    productnametxt.Text = "";
                    dateproduct.SelectedDate = null;
                    showdataproduct();
                    fillcomboproduct();
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void Update_Click(object sender, RoutedEventArgs e)
        {
            if (idproducttxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "Update Production set NameProduct ='" + productnametxt.Text + "', DateProduct = '" + dateproduct.SelectedDate + "' where ID_Product = '" + idproducttxt.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    idproducttxt.Text = "";
                    productnametxt.Text = "";
                    dateproduct.SelectedDate = null;
                    showdataproduct();
                    fillcomboproduct();
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

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

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

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

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

        private void quantitydocument_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (sender is TextBox textBox)
            {
                textBox.Text = new string
               (textBox.Text.Where(ch => (ch >= '0' && ch <= '9')).ToArray());
            }
        }

        private void datadocument_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataGrid gd = (DataGrid)sender;
                DataRowView rowView = gd.SelectedItem as DataRowView;
                if (rowView != null)
                {
                    iddocumenttxt.Text = rowView["ID_Document"].ToString();
                    firstnamepost.Text = rowView["FirstName"].ToString();
                    lastnamepost.Text = rowView["LastName"].ToString();
                    middlenamepost.Text = rowView["MiddleName"].ToString();
                    datedocument.Text = rowView["DateDocument"].ToString();
                    quantitydocument.Text = rowView["Quantity"].ToString();
                    productcombo.Text = rowView["NameProduct"].ToString();
                    productcombo.IsEnabled = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

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

        void showdocument()
        {
            try
            {
                con.Open();
                string sql = "SELECT ID_Document,LastName,FirstName,MiddleName,DateDocument,Quantity,Production.NameProduct FROM Documents inner join Production on Documents.ID_Product = Production.ID_Product";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("Documents");
                dataAdapter.Fill(data);
                datadocument.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                datadocument.Columns[0].Header = "ID";
                datadocument.Columns[1].Header = "Фамилия поставщика";
                datadocument.Columns[2].Header = "Имя поставщика";
                datadocument.Columns[3].Header = "Отчество поставщика";
                datadocument.Columns[4].Header = "Дата документа";
                datadocument.Columns[5].Header = "Количество";
                datadocument.Columns[6].Header = "Продукция";
                datadocument.Columns[0].Visibility = Visibility.Collapsed;
                (datadocument.Columns[4] as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void RefreshDocument_Click(object sender, RoutedEventArgs e)
        {
            iddocumenttxt.Text = "";
            firstnamepost.Text = "";
            lastnamepost.Text = "";
            middlenamepost.Text = "";
            datedocument.SelectedDate = null;
            quantitydocument.Text = "";
            productcombo.Text = "";
            productcombo.IsEnabled = true;
            showdocument();
            fillvsego();
        }

        private void AddDocument_Click(object sender, RoutedEventArgs e)
        {
            if (firstnamepost.Text == "" || lastnamepost.Text == "" || middlenamepost.Text == "" || productcombo.Text == "" || quantitydocument.Text == "" || datedocument.SelectedDate == null)
            {
                MessageBox.Show("Заполните все поля!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Select ID_Product from Production where NameProduct ='" + productcombo.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 idproduct = dataSet.Tables[0].Rows[0]["ID_Product"].ToString();
                        string sql = "INSERT INTO Documents (LastName,FirstName,MiddleName,DateDocument,Quantity,ID_Product) VALUES('" + lastnamepost.Text + "','" + firstnamepost.Text + "','" + middlenamepost.Text + "','" + datedocument.SelectedDate + "','" + quantitydocument.Text + "','" + idproduct.ToString() + "'); INSERT INTO Traffics (ID_TrafficType,TrafficDate,Quantity,ID_Product) VALUES('1','"+datedocument.SelectedDate+ "','" + quantitydocument.Text + "','" + idproduct.ToString() + "')";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        iddocumenttxt.Text = "";
                        firstnamepost.Text = "";
                        lastnamepost.Text = "";
                        middlenamepost.Text = "";
                        datedocument.SelectedDate = null;
                        quantitydocument.Text = "";
                        productcombo.Text = "";
                        showdocument();
                        showtraffic();
                        fillvsego();
                        productcombo.IsEnabled = true;
                    }           
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        private void DeleteDocument_Click(object sender, RoutedEventArgs e)
        {
            if (iddocumenttxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Select ID_Product from Production where NameProduct ='" + productcombo.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 idproduct = dataSet.Tables[0].Rows[0]["ID_Product"].ToString();
                        string sql = "DELETE FROM Documents WHERE ID_Document = '" + iddocumenttxt.Text + "'; INSERT INTO Traffics (ID_TrafficType,TrafficDate,Quantity,ID_Product) VALUES('2',GETDATE(),'" + quantitydocument.Text + "','" + idproduct.ToString() + "')";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        iddocumenttxt.Text = "";
                        firstnamepost.Text = "";
                        lastnamepost.Text = "";
                        middlenamepost.Text = "";
                        datedocument.SelectedDate = null;
                        quantitydocument.Text = "";
                        productcombo.Text = "";
                        productcombo.IsEnabled = true;
                        showdocument();
                        showtraffic();
                        fillvsego();
                    }
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

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

        private void RefreshTraffic_Click(object sender, RoutedEventArgs e)
        {
            idtraffictxt.Text = "";
            showtraffic();
        }

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

        void showtraffic()
        {
            try
            {
                con.Open();
                string sql = "SELECT ID_Traffic,TrafficTypes.TrafficType,TrafficDate,Quantity,Production.NameProduct FROM Traffics inner join Production on Traffics.ID_Product = Production.ID_Product inner join TrafficTypes on Traffics.ID_TrafficType = TrafficTypes.ID_TrafficType";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("Documents");
                dataAdapter.Fill(data);
                datatraffic.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                datatraffic.Columns[0].Header = "ID";
                datatraffic.Columns[1].Header = "Тип движения";
                datatraffic.Columns[2].Header = "Дата движения";
                datatraffic.Columns[3].Header = "Количество";
                datatraffic.Columns[4].Header = "Продукция";
                datatraffic.Columns[0].Visibility = Visibility.Collapsed;
                (datatraffic.Columns[2] as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
    }
}