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;

namespace BorisProject
{
    /// <summary>
    /// Логика взаимодействия для WindowAdmin.xaml
    /// </summary>
    public partial class WindowAdmin : Window
    {
        public WindowAdmin()
        {
            InitializeComponent();
            fillcombosorev();
            fillcombopeopletrain();
        }
        SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=boris;Integrated Security=True");

        //Добавить соревнование
        private void Addsorev_Click(object sender, RoutedEventArgs e)
        {
            if (sorevcombo.Text == "" || nazvtxt.Text == "" || mestotxt.Text == "" || datapic.SelectedDate == null)
            {
                MessageBox.Show("Пустые поля");
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd1 = new SqlCommand("Select ID_trener from Trener where LastName = '" + sorevcombo.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 stridtren = dataSet1.Tables[0].Rows[0]["ID_trener"].ToString();
                        string sql = "INSERT INTO Sorevnovania (Title,Place,Date,ID_trener)  VALUES('" + nazvtxt.Text + "','" + mestotxt.Text + "','" + datapic.SelectedDate + "','" + stridtren.ToString()+ "')";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        MessageBox.Show("Успешно добавлен!");
                    }
                    else
                    {
                        con.Close();
                        MessageBox.Show("Ошибка!");
                    }
                }
                catch (Exception)
                {
                    con.Close();
                    MessageBox.Show("Ошибка!");
                }          
            }
        }

        private void trener_Click(object sender, RoutedEventArgs e)
        {
            trener.Visibility = Visibility.Visible;
            chel.Visibility = Visibility.Hidden;
            sorev.Visibility = Visibility.Hidden;
        }

        private void sorev_Click(object sender, RoutedEventArgs e)
        {
            sorev.Visibility = Visibility.Visible;
            trener.Visibility = Visibility.Hidden;
            chel.Visibility = Visibility.Hidden;
        }

        private void chel_Click(object sender, RoutedEventArgs e)
        {
            chel.Visibility = Visibility.Visible;
            sorev.Visibility = Visibility.Hidden;
            trener.Visibility = Visibility.Hidden;
        }

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

        private void Addtrener_Click(object sender, RoutedEventArgs e)
        {
            if (trenerfamiliatxt.Text == "" || trenernametxt.Text == "" || trenerotchestvotxt.Text == "" || trenerdatepic.SelectedDate == null || logintxt.Text == "" || passwordtxt.Password == "")
            {
                MessageBox.Show("Пустые поля!");
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "INSERT INTO Trener (LastName,FirstName,MiddleName,DOB,Login,Password)  VALUES('" + trenerfamiliatxt.Text + "','" + trenernametxt.Text + "','" + trenerotchestvotxt.Text + "','" + trenerdatepic.SelectedDate + "','" + logintxt.Text + "','" + passwordtxt.Password + "')";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("Тренер добавлен!");
                }
                catch
                {
                    con.Close();
                    MessageBox.Show("Ошибка");
                }
            }
        }

        private void Deletetrener_Click(object sender, RoutedEventArgs e)
        {
            if (Idtrener.Text == "")
            {
                MessageBox.Show("ID не указано");
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "DELETE FROM Trener WHERE ID_trener = '" + Idtrener.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("Тренер удален!");
                }
                catch
                {
                    con.Close();
                    MessageBox.Show("Ошибка");
                }
            }
        }

        private void Updatetrener_Click(object sender, RoutedEventArgs e)
        {
            if (Idtrener.Text == "")
            {
                MessageBox.Show("ID не указано");
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "Update Trener set LastName ='" + trenerfamiliatxt.Text + "', FirstName = '" + trenernametxt.Text + "', MiddleName = '" + trenerotchestvotxt.Text + "', DOB = '" + trenerdatepic.SelectedDate + "', Login = '" + logintxt.Text + "', Password = '" + passwordtxt.Password + "' where ID_trener = '" + Idtrener.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("Тренер изменен");
                }
                catch
                {
                    con.Close();
                    MessageBox.Show("Ошибка");
                }
            }
        }

        private void showtabletrener_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                con.Open();
                string rke = "SELECT * From Trener";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(rke, con);
                DataTable data = new DataTable("Trener");
                dataAdapter.Fill(data);
                datagridtrener.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                datagridtrener.Columns[0].Header = "ID";
                datagridtrener.Columns[1].Header = "Фамилия";
                datagridtrener.Columns[2].Header = "Имя";
                datagridtrener.Columns[3].Header = "Отчество";
                datagridtrener.Columns[4].Header = "Дата рождения";
                datagridtrener.Columns[5].Header = "Логин";
                datagridtrener.Columns[6].Header = "Пароль";
            }
            catch
            {
                con.Close();
                MessageBox.Show("Ошибка");
            }
        }

       void fillcombosorev()
        {
            try
            {
                sorevcombo.Items.Clear();
                con.Open();
                SqlCommand sql = con.CreateCommand();
                sql.CommandType = CommandType.Text;        
                sql.CommandText = "Select LastName from Trener";
                sql.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sql);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    sorevcombo.Items.Add(dr["LastName"].ToString());
                }
                con.Close();
            }
            catch
            {
                con.Close();
                MessageBox.Show("Ошибка");
            }
        }

        private void showsorevtable_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                con.Open();
                string rke = "SELECT ID_sorev,Title,Place,[Date], Trener.LastName From Sorevnovania inner join Trener on Trener.ID_trener = Sorevnovania.ID_trener";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(rke, con);
                DataTable data = new DataTable("Sorevnovania");
                dataAdapter.Fill(data);
                datasorev.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                datasorev.Columns[0].Header = "ID";
                datasorev.Columns[1].Header = "Название";
                datasorev.Columns[2].Header = "Место";
                datasorev.Columns[3].Header = "Дата";
                datasorev.Columns[4].Header = "Тренер";
            }
            catch
            {
                con.Close();
                MessageBox.Show("Ошибка");
            }
        }
        void fillcombopeopletrain()
        {
            try
            {
                combotrener.Items.Clear();
                con.Open();
                SqlCommand sql = con.CreateCommand();
                sql.CommandType = CommandType.Text;
                sql.CommandText = "Select LastName from Trener";
                sql.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sql);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    combotrener.Items.Add(dr["LastName"].ToString());
                }
                con.Close();
            }
            catch
            {
                con.Close();
                MessageBox.Show("Ошибка");
            }
        }

        private void deletesorev_Click(object sender, RoutedEventArgs e)
        {
            if (idsorevtxt.Text == "")
            {
                MessageBox.Show("ID не указано");
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "DELETE FROM Sorevnovania WHERE ID_sorev = '" + idsorevtxt.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("Тренер удален!");
                }
                catch
                {
                    con.Close();
                    MessageBox.Show("Ошибка");
                }
            }
        }

        private void Updatesorev_Click(object sender, RoutedEventArgs e)
        {
            if (idsorevtxt.Text == "")
            {
                MessageBox.Show("ID не указано");
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd1 = new SqlCommand("Select ID_trener from Trener where LastName = '" + sorevcombo.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 stridtren = dataSet1.Tables[0].Rows[0]["ID_trener"].ToString();
                        string sql = "Update Sorevnovania set Title ='" + nazvtxt.Text + "', Place = '" + mestotxt.Text + "', Date = '" + datapic.SelectedDate + "', ID_trener = '" + stridtren.ToString() + "' where ID_sorev = '" + idsorevtxt.Text + "'";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        MessageBox.Show("Успешно изменен!");
                    }
                    else
                    {
                        con.Close();
                        MessageBox.Show("Ошибка combo!");
                    }
                }
                catch
                {
                    con.Close();
                     MessageBox.Show("Ошибка");
                }
            }
        }

        private void Addchel_Click(object sender, RoutedEventArgs e)
        {
            if (chelnametxt.Text==""||chelfamiliatxt.Text==""||chelotchestxt.Text==""||cheldatepic.SelectedDate==null||combotrener.Text=="")
            {
                MessageBox.Show("Пустые поля");
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd1 = new SqlCommand("Select ID_trener from Trener where LastName = '" + combotrener.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 stridtren = dataSet1.Tables[0].Rows[0]["ID_trener"].ToString();
                        string sql = "INSERT INTO PeopleTrain (FirstName,LastName,MiddleName,DOB,ID_Trener)  VALUES('" + chelnametxt.Text  + "','" + chelfamiliatxt.Text + "','" + chelotchestxt.Text + "','" + cheldatepic.SelectedDate + "','" + stridtren.ToString() + "')";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        MessageBox.Show("Успешно добавлен!");
                    }
                    else
                    {
                        con.Close();
                        MessageBox.Show("Ошибка!");
                    }
                }
                catch (Exception)
                {
                    con.Close();
                    MessageBox.Show("Ошибка!");
                }
            }
        }

        private void chelshowtable_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                con.Open();
                string rke = "SELECT ID_People,PeopleTrain.FirstName,PeopleTrain.LastName,PeopleTrain.MiddleName,PeopleTrain.DOB,Trener.LastName From PeopleTrain inner join Trener on Trener.ID_trener = PeopleTrain.ID_Trener";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(rke, con);
                DataTable data = new DataTable("PeopleTraim");
                dataAdapter.Fill(data);
                cheldata.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                cheldata.Columns[0].Header = "ID";
                cheldata.Columns[1].Header = "Имя";
                cheldata.Columns[2].Header = "Фамилия";
                cheldata.Columns[3].Header = "Отчество";
                cheldata.Columns[4].Header = "Дата рождения";
                cheldata.Columns[5].Header = "Тренер";
            }
            catch
            {
                con.Close();
                MessageBox.Show("Ошибка");
            }
        }

        private void deletechel_Click(object sender, RoutedEventArgs e)
        {
            if (chelid.Text == "")
            {
                MessageBox.Show("ID не указано");
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "DELETE FROM PeopleTrain WHERE ID_people = '" + chelid.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("Запись удалена!");
                }
                catch
                {
                    con.Close();
                    MessageBox.Show("Ошибка");
                }
            }
        }

        private void updatechel_Click(object sender, RoutedEventArgs e)
        {
            if (chelid.Text == "")
            {
                MessageBox.Show("ID не указано");
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd1 = new SqlCommand("Select ID_trener from Trener where LastName = '" + combotrener.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 stridtren = dataSet1.Tables[0].Rows[0]["ID_trener"].ToString();
                        string sql = "Update PeopleTrain set FirstName ='" + chelnametxt.Text + "', LastName = '" + chelfamiliatxt.Text + "', MiddleName = '" + chelotchestxt.Text + "', DOB = '" + cheldatepic.SelectedDate + "', ID_Trener = '" + stridtren.ToString() + "' where ID_people = '" + chelid.Text + "'";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        MessageBox.Show("Успешно изменен!");
                    }
                    else
                    {
                        con.Close();
                        MessageBox.Show("Ошибка combo!");
                    }
                }
                catch
                {
                    con.Close();
                    MessageBox.Show("Ошибка");
                }
            }
        }
    }
}