using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace WindowsFormsAppDB
{
    public partial class Form1 : Form
    {
        SqlConnection sqlConnection = null;
        //static SqlCommand commandCatalogInMusicStore;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            this.toolsTableAdapter.Fill(this.agriculturalMachineryDataSet.Tools);

            this.orderTableAdapter.Fill(this.agriculturalMachineryDataSet.Order);

            sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["AgriculturalMachinery"].ConnectionString);

            sqlConnection.Open();

            //if(sqlConnection.State == ConnectionState.Open)
            //    MessageBox.Show("Connected good!");
        }

        string SELECTIDENTITY(SqlCommand command, string IdVal) =>
            command.CommandText = $"SELECT IDENT_CURRENT('{IdVal}')";


        //private void button3_Click(object sender, EventArgs e)
        //{
        //    if(tB_IdCustomer.Text != string.Empty || tb_NameCustomer.Text != string.Empty || tB_PhoneNumber.Text != string.Empty || tb_Address.Text != string.Empty || tb_CurrentAccount.Text != string.Empty)
        //    {

        //        //Додаємо Customers
        //        SqlCommand commandCustomers = new SqlCommand(
        //            "INSERT INTO [Customers] (IdCustomer, NameCustomer, PhoneNumber, Address, CurrentAccount) " +
        //            "VALUES (@IdCustomer, @NameCustomer, @PhoneNumber, @Address, @CurrentAccount)", sqlConnection);

        //        commandCustomers.Parameters.AddWithValue("IdCustomer", tB_IdCustomer.Text);
        //        commandCustomers.Parameters.AddWithValue("NameCustomer", tb_NameCustomer.Text);
        //        commandCustomers.Parameters.AddWithValue("PhoneNumber", tB_PhoneNumber.Text);
        //        commandCustomers.Parameters.AddWithValue("Address", tb_Address.Text);
        //        commandCustomers.Parameters.AddWithValue("CurrentAccount", tb_CurrentAccount.Text);

        //        MessageBox.Show(commandCustomers.ExecuteNonQuery().ToString());
        //        sqlConnection.Close();
        //    }
        //    else
        //        MessageBox.Show("Будь ласка, введіть дані!");
        //}

        //private void button4_Click(object sender, EventArgs e)
        //{
        //    if(tb_IdTool.Text != string.Empty || tb_NameTool.Text != string.Empty || tb_NumberTool.Text != string.Empty)
        //    {
        //        sqlConnection.Open();

        //        //Додаємо Tools
        //        SqlCommand commandTools = new SqlCommand(
        //        "INSERT INTO [Tools] (IdTool, NameTool, NumberTool) " +
        //        "VALUES (@IdTool, @NameTool, @NumberTool)", sqlConnection);

        //        commandTools.Parameters.AddWithValue("IdTool", tb_IdTool.Text);
        //        commandTools.Parameters.AddWithValue("NameTool", tb_NameTool.Text);
        //        commandTools.Parameters.AddWithValue("NumberTool", tb_NumberTool.Text);


        //        MessageBox.Show(commandTools.ExecuteNonQuery().ToString());
        //        sqlConnection.Close();

        //    }
        //    else
        //        MessageBox.Show("Будь ласка, введіть дані!");
        //}
        private void tb_Insert_Click(object sender, EventArgs e)
        {
            if(tB_Id.Text != string.Empty || tB_CustomerId.Text != string.Empty || tb_ToolId.Text != string.Empty || tb_Number.Text != string.Empty)
            {
                sqlConnection.Open();

                //Додаємо Order
                SqlCommand commandOrder = new SqlCommand(
                "INSERT INTO [Order] (Id, CustomerId, ToolId, Number, Date, Done_NotDone) " +
                "VALUES (@Id, @CustomerId, @ToolId, @Number, @Date, @Done_NotDone)", sqlConnection);

                commandOrder.Parameters.AddWithValue("Id", tB_Id.Text);
                commandOrder.Parameters.AddWithValue("CustomerId", tB_CustomerId.Text);
                commandOrder.Parameters.AddWithValue("ToolId", tb_ToolId.Text);
                commandOrder.Parameters.AddWithValue("Number", tb_Number.Text);
                commandOrder.Parameters.AddWithValue("Date", tb_Date.Text);
                commandOrder.Parameters.AddWithValue("Done_NotDone", chB_Done_NotDone.Checked);

                MessageBox.Show(commandOrder.ExecuteNonQuery().ToString());
                sqlConnection.Close();

            }
            else
                MessageBox.Show("Будь ласка, введіть дані!");

            //SELECTIDENTITY(commandRecordings, "Recordings");

            //var c = commandOrder.ExecuteScalar().ToString();
            // MessageBox.Show(c);
            //Done_NotDone(chB_Done_NotDone.ToString());




            //SELECTIDENTITY(commandPerformers, "Performers");

            //var c1 = commandPerformers.ExecuteScalar().ToString();




            //SELECTIDENTITY(commandGenres, "Genres");

            //var c2 = commandGenres.ExecuteScalar().ToString();


            //// Додаємо RecordCompanys
            //SqlCommand commandRecordCompanys = new SqlCommand(
            //    "INSERT INTO [RecordCompanys] (RecordCompany) " +
            //    "VALUES (@RecordCompany)", sqlConnection);

            //commandRecordCompanys.Parameters.AddWithValue("RecordCompany", t_B_RecordCompanys.Text);

            //commandRecordCompanys.ExecuteNonQuery().ToString();

            //SELECTIDENTITY(commandRecordCompanys, "RecordCompanys");

            //var c4 = commandRecordCompanys.ExecuteScalar().ToString();


            ////Додаємо InformationAboutPerformers
            //SqlCommand commandInformationAboutPerformers = new SqlCommand(
            //    "INSERT INTO [InfoAboutPerformers] (FirstName, LastName, DateOfBirth, PlaceOfBirth, Professions, Groups) " +
            //    "VALUES (@FirstName, @LastName, @DateOfBirth, @PlaceOfBirth, @Professions, @Groups)", sqlConnection);

            //commandInformationAboutPerformers.Parameters.AddWithValue("FirstName", tb_FirstName.Text);
            //commandInformationAboutPerformers.Parameters.AddWithValue("LastName", tb_LastName.Text);
            //commandInformationAboutPerformers.Parameters.AddWithValue("DateOfBirth", tB_DateOfBirth.Text);
            //commandInformationAboutPerformers.Parameters.AddWithValue("PlaceOfBirth", tb_PlaceOfBirth.Text);
            //commandInformationAboutPerformers.Parameters.AddWithValue("Professions", tb_Professions.Text);
            //commandInformationAboutPerformers.Parameters.AddWithValue("Groups", tb_Groups.Text);

            //commandInformationAboutPerformers.ExecuteNonQuery().ToString();

            //SELECTIDENTITY(commandInformationAboutPerformers, "InfoAboutPerformers");

            //var c3 = commandInformationAboutPerformers.ExecuteScalar().ToString();

            //commandCatalogInMusicStore = new SqlCommand("INSERT INTO [CatalogInMusicStore] (IdRecords, IdPerformers, IdGenres, IdRecordCompanys, Price, Quantity, NumberOfSales, IdInformationAboutPerformers) " +
            //    "VALUES (@IdRecords, @IdPerformers, @IdGenres, @IdRecordCompanys, @Price, @Quantity, @NumberOfSales, @IdInformationAboutPerformers)", sqlConnection);



            //commandCatalogInMusicStore.Parameters.AddWithValue("IdRecords", c);

            //MessageBox.Show(c);

            //commandCatalogInMusicStore.Parameters.AddWithValue("IdPerformers", c1);

            //commandCatalogInMusicStore.Parameters.AddWithValue("IdGenres", c2);




            //commandCatalogInMusicStore.Parameters.AddWithValue("IdRecordCompanys", c4);


            //commandCatalogInMusicStore.Parameters.AddWithValue("Price", tB_Id.Text);
            //commandCatalogInMusicStore.Parameters.AddWithValue("Quantity", tB_CustomerId.Text);
            //commandCatalogInMusicStore.Parameters.AddWithValue("NumberOfSales", tb_ToolId.Text);


            //commandCatalogInMusicStore.Parameters.AddWithValue("IdInformationAboutPerformers", c3);
            //commandCatalogInMusicStore.ExecuteNonQuery().ToString();
        }




        private void btn_Album_Click(object sender, EventArgs e)
        {
            if(tb_Album.Text != string.Empty)
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter($"select Id as 'Код замовлення', IdTool 'Код інструмента', NameTool as 'Назва інструмента' from [Order], [Tools]where NameTool = N'{tb_Album.Text}'", sqlConnection);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet);
                dataGridView1.DataSource = dataSet.Tables[0];

            }
            else
                MessageBox.Show("Будь ласка, введіть дані!");


        }


        private void btn_Perfomers_Click(object sender, EventArgs e)
        {
            if(tb_Perfomers.Text != string.Empty)
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter($"SELECT CatalogInMusicStore.Id, Performers.Performer, Genres.Genre, RecordCompanys.RecordCompany,CatalogInMusicStore.Price,  CatalogInMusicStore.Quantity FROM CatalogInMusicStore, Performers, Genres, Recordings, RecordCompanys WHERE CatalogInMusicStore.IdPerformers = Performers.IdPerformer AND CatalogInMusicStore.IdGenres = Genres.IdGenre AND CatalogInMusicStore.IdRecords = Recordings.IdRecord AND CatalogInMusicStore.IdRecordCompanys = RecordCompanys.IdRecordCompany AND Performers.Performer = '{tb_Perfomers.Text}'", sqlConnection);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet);
                dataGridView1.DataSource = dataSet.Tables[0];

                SqlDataAdapter dataAdapter1 = new SqlDataAdapter($"SELECT Recordings.AlbumTitle, Recordings.DateOfSale, Recordings.NumberOfSales FROM CatalogInMusicStore, Recordings, RecordCompanys, Performers WHERE CatalogInMusicStore.IdRecords = Recordings.IdRecord AND CatalogInMusicStore.IdPerformers = Performers.IdPerformer AND CatalogInMusicStore.IdRecordCompanys = RecordCompanys.IdRecordCompany AND Performers.Performer = '{tb_Perfomers.Text}'", sqlConnection);

                DataSet dataSet1 = new DataSet();

                dataAdapter1.Fill(dataSet1);
                dataGridView2.DataSource = dataSet1.Tables[0];

                SqlDataAdapter dataAdapter2 = new SqlDataAdapter($"SELECT InfoAboutPerformers.FirstName, InfoAboutPerformers.LastName, InfoAboutPerformers.DateOfBirth,  InfoAboutPerformers.PlaceOfBirth, InfoAboutPerformers.Professions, InfoAboutPerformers.Groups FROM CatalogInMusicStore, InfoAboutPerformers, RecordCompanys, Performers WHERE CatalogInMusicStore.IdInformationAboutPerformers = InfoAboutPerformers.IdPerfomersInfo AND CatalogInMusicStore.IdInformationAboutPerformers = RecordCompanys.IdRecordCompany AND Performers.Performer = '{tb_Perfomers.Text}'", sqlConnection);

                DataSet dataSet2 = new DataSet();

                dataAdapter2.Fill(dataSet2);
                dataGridView3.DataSource = dataSet2.Tables[0];
            }
            else
                MessageBox.Show("Будь ласка, введіть дані!");
        }

        private void btn_RecordCompanys_Click(object sender, EventArgs e)
        {
            if(tb_RecordCompanys.Text != string.Empty)
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter($"SELECT Performers.Performer, Genres.Genre, RecordCompanys.RecordCompany, CatalogInMusicStore.Price, CatalogInMusicStore.Quantity FROM CatalogInMusicStore, Performers, Genres, Recordings, RecordCompanys WHERE CatalogInMusicStore.IdPerformers = Performers.IdPerformer AND CatalogInMusicStore.IdGenres = Genres.IdGenre and CatalogInMusicStore.IdRecords = Recordings.IdRecord and CatalogInMusicStore.IdRecordCompanys = RecordCompanys.IdRecordCompany and RecordCompanys.RecordCompany = '{tb_RecordCompanys.Text}'", sqlConnection);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet);
                dataGridView1.DataSource = dataSet.Tables[0];

                SqlDataAdapter dataAdapter1 = new SqlDataAdapter($"SELECT Recordings.AlbumTitle,  Recordings.DateOfSale, Recordings.NumberOfSales FROM CatalogInMusicStore, Recordings, RecordCompanys WHERE CatalogInMusicStore.IdRecords = Recordings.IdRecord AND CatalogInMusicStore.IdRecordCompanys = RecordCompanys.IdRecordCompany AND RecordCompanys.RecordCompany = '{tb_RecordCompanys.Text}'", sqlConnection);

                DataSet dataSet1 = new DataSet();

                dataAdapter1.Fill(dataSet1);
                dataGridView2.DataSource = dataSet1.Tables[0];

                SqlDataAdapter dataAdapter2 = new SqlDataAdapter($"SELECT InfoAboutPerformers.FirstName, InfoAboutPerformers.LastName, InfoAboutPerformers.DateOfBirth, InfoAboutPerformers.PlaceOfBirth, InfoAboutPerformers.Professions, InfoAboutPerformers.Groups FROM CatalogInMusicStore, InfoAboutPerformers, RecordCompanys WHERE CatalogInMusicStore.IdInformationAboutPerformers = InfoAboutPerformers.IdPerfomersInfo AND CatalogInMusicStore.IdRecordCompanys = RecordCompanys.IdRecordCompany AND RecordCompanys.RecordCompany = '{tb_RecordCompanys.Text}'", sqlConnection);

                DataSet dataSet2 = new DataSet();

                dataAdapter2.Fill(dataSet2);
                dataGridView3.DataSource = dataSet2.Tables[0];
            }
            else
                MessageBox.Show("Будь ласка, введіть дані!");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd;
            string cbox = comboBox1.SelectedItem.ToString();
            if(cbox == "Кількість продаж")
            {
                cmd = new SqlCommand("SELECT COUNT(select sum(CatalogInMusicStore.Quantity) from CatalogInMusicStore", sqlConnection);

                int countSale = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                cmd.CommandText = "SELECT COUNT(select sum(CatalogInMusicStore.Quantity) from CatalogInMusicStore";
                //MessageBox.Show("Загальна кількість продаж: " + cmd.);
            }
            else if(cbox == "Кількість альбомів")
            {
                cmd = new SqlCommand("SELECT COUNT(DISTINCT Recordings.AlbumTitle) FROM Recordings", sqlConnection);

                int countAlbum = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();

                MessageBox.Show("Загальна кількість альбомів: " + countAlbum.ToString());
            }
        }


        private void button1_Click_1(object sender, EventArgs e)
        {
            SqlCommand cmd;
            string cbox = comboBox1.SelectedItem.ToString();
            if(cbox == "Кількість продаж")
            {
                cmd = new SqlCommand("SELECT sum(CatalogInMusicStore.NumberOfSales) from CatalogInMusicStore", sqlConnection);

                int countSale = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                //cmd.CommandText = "SELECT COUNT(select sum(CatalogInMusicStore.Quantity) from CatalogInMusicStore";
                MessageBox.Show("Загальна кількість продаж: " + countSale.ToString());
            }
            else if(cbox == "Кількість альбомів")
            {
                cmd = new SqlCommand("SELECT COUNT(DISTINCT Recordings.AlbumTitle) FROM Recordings", sqlConnection);

                int countAlbum = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();

                MessageBox.Show("Загальна кількість альбомів: " + countAlbum.ToString());
            }
        }

        private void btn_Clean_Click(object sender, EventArgs e)
        {

        }

        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void comboBox3_SelectedIndexChanged_1(object sender, EventArgs e)
        {

            if(comboBox3.SelectedIndex == 0)
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from [Tools]", sqlConnection);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet);
                dataGridView4.DataSource = dataSet.Tables[0];

            }
            if(comboBox3.SelectedIndex == 1)
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from [Customers]", sqlConnection);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet);
                dataGridView4.DataSource = dataSet.Tables[0];

            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            if(comboBox3.Text == "Ітструменти")
            {
                MessageBox.Show("__");
            }
        }

        private void comboBox2_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            //if(comboBox2.SelectedIndex == 0)
            //{
            //    MessageBox.Show(comboBox2.Text.ToString());
            //    SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from [Tools]", sqlConnection);

            //    DataSet dataSet = new DataSet();

            //    dataAdapter.Fill(dataSet);
            //    dataGridView4.DataSource = dataSet.Tables[0];

            //}
        }

        private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlDataAdapter dataAdapter = new SqlDataAdapter($"select Id as 'Код замовлення', IdTool as 'Код інструмента', NameTool as 'Назва інструмента' from [Order] join [Tools] on IdTool = ToolId where NameTool = N'{comboBox4.Text}'", sqlConnection);

            DataSet dataSet = new DataSet();

            dataAdapter.Fill(dataSet);
            dataGridView4.DataSource = dataSet.Tables[0];
        }
    }
}