using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace kyrc_fehazepama
{
    public partial class Form1 : Form
    {
        SqlConnection sqlConnection;
        public Form1()
        {
            InitializeComponent();
        }
        private async void Form1_Load(object sender, EventArgs e)
        {
            string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\ZZZ\source\repos\kyrc_fehazepama\maga.mdf;Integrated Security=True;Connect Timeout=30";
            sqlConnection = new SqlConnection(connectionString);
            await sqlConnection.OpenAsync();
            SqlDataReader sqlReader = null;
            SqlCommand command = new SqlCommand("SELECT *FROM[name1],[part],[otcet1]", sqlConnection);
            try
            {
                sqlReader = await command.ExecuteReaderAsync();

                while (await sqlReader.ReadAsync())
                {
                    //name,got,idpa,jahr,ceha,kolvo,idto
                    listBox1.Items.Add(Convert.ToString(sqlReader["name"]) + " " + Convert.ToString(sqlReader["got"]) + "   " + Convert.ToString(sqlReader["idpa"]) + "          " + Convert.ToString(sqlReader["jahr"]) + "          " + Convert.ToString(sqlReader["kolvo"]) + "   " + Convert.ToString(sqlReader["idto"]) + "   " + Convert.ToString(sqlReader["ceha"]));
                    listBox2.Items.Add(Convert.ToString(sqlReader["idpa"]) + "                                " + Convert.ToString(sqlReader["pot"]) + "           " + Convert.ToString(sqlReader["pol"]) + "             " + Convert.ToString(sqlReader["oct"]));
                    listBox3.Items.Add(Convert.ToString(sqlReader["idpa"]) + "                      " + Convert.ToString(sqlReader["tovpo"]) + "                   " + Convert.ToString(sqlReader["tovpro"]) + "          " + Convert.ToString(sqlReader["tovoct"]));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(), ex.Source.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            finally
            {
                if (sqlReader != null)
                    sqlReader.Close();
            }
        }
        private void выходToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (sqlConnection != null && sqlConnection.State != ConnectionState.Closed)
                sqlConnection.Close();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (sqlConnection != null && sqlConnection.State != ConnectionState.Closed)
                sqlConnection.Close();
        }


        private async void обновитьToolStripMenuItem_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            listBox2.Items.Clear();
            listBox3.Items.Clear();
            SqlDataReader sqlReader = null;
            SqlCommand command = new SqlCommand("SELECT *FROM[name1],[otcet1],[part]", sqlConnection);
            try
            {
                sqlReader = await command.ExecuteReaderAsync();

                while (await sqlReader.ReadAsync())
                {
                    listBox1.Items.Add(Convert.ToString(sqlReader["name"]) + " " + Convert.ToString(sqlReader["got"]) + "   " + Convert.ToString(sqlReader["idpa"]) + "          " + Convert.ToString(sqlReader["jahr"]) + "          " + Convert.ToString(sqlReader["kolvo"]) + "   " + Convert.ToString(sqlReader["idto"]) + "   " + Convert.ToString(sqlReader["ceha"]));
                    listBox2.Items.Add(Convert.ToString(sqlReader["idpa"]) + "                                " + Convert.ToString(sqlReader["pot"]) + "           " + Convert.ToString(sqlReader["pol"]) + "             " + Convert.ToString(sqlReader["oct"]));
                    listBox3.Items.Add(Convert.ToString(sqlReader["idpa"]) + "                      " + Convert.ToString(sqlReader["tovpo"]) + "                 " + Convert.ToString(sqlReader["tovpro"]) + "       " + Convert.ToString(sqlReader["tovoct"]));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(), ex.Source.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (sqlReader != null)
                    sqlReader.Close();
            }
        }
        private async void button2_Click(object sender, EventArgs e)
        {
            SqlCommand command = new SqlCommand("INSERT [name1] (name,got,idpa,jahr,ceha,kolvo,idto)VALUES (@name,@got,@idpa,@jahr,@ceha,@kolvo,@idto)", sqlConnection);
            command.Parameters.AddWithValue("name", textBox4.Text);
            command.Parameters.AddWithValue("got", textBox5.Text);
            command.Parameters.AddWithValue("idpa", textBox6.Text);
            command.Parameters.AddWithValue("jahr", textBox7.Text);
            command.Parameters.AddWithValue("ceha", textBox30.Text);
            command.Parameters.AddWithValue("kolvo", textBox31.Text);
            command.Parameters.AddWithValue("idto", textBox32.Text);
            await command.ExecuteNonQueryAsync();
        }
        private async void button3_Click(object sender, EventArgs e)
        {
            SqlCommand command = new SqlCommand("UPDATE [name1] SET [name]=@name,[got]=@got,[jahr]=@jahr,[ceha]=@ceha,[kolvo]=@kolvo,[idpa] = @idpa WHERE [idto]=@idto", sqlConnection);
            command.Parameters.AddWithValue("name", textBox8.Text);
            command.Parameters.AddWithValue("got", textBox9.Text);
            command.Parameters.AddWithValue("idpa", textBox10.Text);
            command.Parameters.AddWithValue("jahr", textBox11.Text);
            command.Parameters.AddWithValue("ceha", textBox33.Text);
            command.Parameters.AddWithValue("kolvo", textBox34.Text);
            command.Parameters.AddWithValue("idto", textBox35.Text);
            await command.ExecuteNonQueryAsync();
        }
      //  private async void button1_Click(object sender, EventArgs e)
       // {
            //SqlCommand command = new SqlCommand("DELETE FROM [name1] WHERE [idto]=@idto", sqlConnection);
            //command.Parameters.AddWithValue("idto", textBox1.Text);
           // await command.ExecuteNonQueryAsync();
      //  }


        private async void button6_Click(object sender, EventArgs e)
        {
            SqlCommand command = new SqlCommand("INSERT [otcet1] (idpa,pot,pol,oct)VALUES (@idpa,@pot,@pol,@oct)", sqlConnection);
            command.Parameters.AddWithValue("idpa", textBox19.Text);
            command.Parameters.AddWithValue("pot", textBox18.Text);
            command.Parameters.AddWithValue("pol", textBox17.Text);
            command.Parameters.AddWithValue("oct", textBox16.Text);
            await command.ExecuteNonQueryAsync();
        }

        private async void button4_Click(object sender, EventArgs e)
        {
            SqlCommand command = new SqlCommand("UPDATE [otcet1] SET [pot]=@pot,[pol]=@pol,[oct]=@oct WHERE [idpa]=@idpa", sqlConnection);
            command.Parameters.AddWithValue("idpa", textBox15.Text);
            command.Parameters.AddWithValue("pot", textBox14.Text);
            command.Parameters.AddWithValue("pol", textBox13.Text);
            command.Parameters.AddWithValue("oct", textBox12.Text);
            await command.ExecuteNonQueryAsync();
        }

        private async void button9_Click(object sender, EventArgs e)
        {
            SqlCommand command = new SqlCommand("INSERT [part] (idpa,tovpo,tovpro,tovoct)VALUES (@idpa,@tovpo,@tovpro,@tovoct)",sqlConnection);
            command.Parameters.AddWithValue("idpa", textBox28.Text);
            command.Parameters.AddWithValue("tovpro", textBox27.Text);
            command.Parameters.AddWithValue("tovpo", textBox26.Text);
            command.Parameters.AddWithValue("tovoct", textBox25.Text);
            await command.ExecuteNonQueryAsync();
        }

        private async void button7_Click(object sender, EventArgs e)
        {
            SqlCommand command = new SqlCommand("UPDATE [part] SET [tovpo]=@tovpo,[tovpro]=@tovpro,[tovoct]=@tovoct WHERE [idpa]=@idpa", sqlConnection);
            command.Parameters.AddWithValue("idpa", textBox24.Text);
            command.Parameters.AddWithValue("tovpro", textBox23.Text);
            command.Parameters.AddWithValue("tovpo", textBox22.Text);
            command.Parameters.AddWithValue("tovoct", textBox21.Text);
            await command.ExecuteNonQueryAsync();
        }
        private async void button8_Click(object sender, EventArgs e)
        {
            SqlCommand command = new SqlCommand("DELETE FROM [part] WHERE [idpa]=@idpa", sqlConnection);
            command.Parameters.AddWithValue("idpa", textBox29.Text);

            await command.ExecuteNonQueryAsync();
        }

        private void textBox19_TextChanged(object sender, EventArgs e)
        {

        }

        private void label33_Click(object sender, EventArgs e)
        {

        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {

        }

        private async void button10_Click(object sender, EventArgs e)
       {

       }

        private void label34_Click(object sender, EventArgs e)
        {

        }
    }


}