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

namespace Kapazitätsberechnung
{
    public partial class Form1 : Form
    {
        DataTable ds;
        SqlDataAdapter sqlGridDa;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            using (SqlConnection sqlCon = new SqlConnection(GlobalVariables.connectionKapazität))
            {
                sqlCon.Open();
                SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT * FROM PW", sqlCon);
                DataTable dtbl = new DataTable();
                sqlDa.Fill(dtbl);

                cbKategorie.DataSource = dtbl;
                cbKategorie.DisplayMember = "ID";


            }
        }

        private void bDgvfüllen_Click(object sender, EventArgs e)
        {
            string queryFill;
            string pwname = cbKategorie.Text + tbJahr.Text;
            string queryCreate = @"if not exists(select name from sysobjects where name='dbo" + pwname + "')" +
                                 "CREATE TABLE dbo" + pwname + " (ID int NOT NULL, Mitarbeitername varchar(50), Werk varchar(10), Straße varchar(10), Vormontage varchar(10), Oberfläche varchar(10), Endanschlag varchar(10), PRIMARY KEY(ID))" +
                                 "if not exists(select ID from dbo" + pwname + " WHERE ID = 1)" +
                                 "INSERT INTO dbo" + pwname + "(ID, Mitarbeitername, Werk)" +
                                 "SELECT ID, Mitarbeitername, Werk FROM Namen;";
            using (SqlConnection sqlCon = new SqlConnection(GlobalVariables.connectionKapazität))
            {
                GlobalVariables.dtFromGrid.Clear();
                sqlCon.Open();
                SqlCommand cmdcreate = new SqlCommand(queryCreate, sqlCon);
                cmdcreate.ExecuteNonQuery();
                if (cbWerk.Text == "Alles")
                {
                    queryFill = "SELECT * from dbo"+pwname+"";
                }
                else
                {
                    queryFill = "SELECT * from dbo" + pwname + " WHERE Werk LIKE '"+cbWerk.Text+"'";
                }
                sqlGridDa = new SqlDataAdapter(queryFill, sqlCon);
                ds = new DataTable();
                sqlGridDa.Fill(ds);
                dgv.DataSource = ds;

            }
        }
        private void dgv_CellEnter(object sender, DataGridViewCellEventArgs e)
        {

            string name = "dbo" + cbKategorie.Text + tbJahr.Text;
            string queryUpdate = @"UPDATE " + name + " SET ";

            using (SqlConnection sqlCon = new SqlConnection(GlobalVariables.connectionKapazität))
                sqlCon.Open();
            GlobalVariables.dtbl = dgv.DataSource as DataTable;
            SqlCommandBuilder update = new SqlCommandBuilder(GlobalVariables.sqlGridDa);
            GlobalVariables.sqlGridDa.UpdateCommand = update.GetUpdateCommand();
            GlobalVariables.sqlGridDa.Update(GlobalVariables.dtFromGrid);
            using (SqlConnection sqlCon = new SqlConnection(GlobalVariables.connectionKapazität))
            {
                dtFromGrid = dgv.DataSource as DataTable;

                sqlCon.Open();
                SqlCommand cmdDelete = new SqlCommand("TRUNCATE TABLE " + name + "", sqlCon);
                cmdDelete.ExecuteNonQuery();
                using (SqlBulkCopy s = new SqlBulkCopy(sqlCon))
                {
                    s.DestinationTableName = name;
                    s.ColumnMappings.Add("ID", "ID");
                    s.ColumnMappings.Add("Mitarbeitername", "Mitarbeitername");
                    s.ColumnMappings.Add("Werk", "Werk");
                    s.ColumnMappings.Add("Straße", "Straße");
                    s.ColumnMappings.Add("Vormontage", "Vormontage");
                    s.ColumnMappings.Add("Oberfläche", "Oberfläche");
                    s.ColumnMappings.Add("Endanschlag", "Endanschlag");
                    s.WriteToServer(dtFromGrid);
                }

            }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string name = "dbo" + cbKategorie.Text + tbJahr.Text;
                string queryUpdate = @"UPDATE " + name + " SET Straße='" + ds.Columns["Straße"] + "', Vormontage='" + ds.Columns["Vormontage"] + "',Oberfläche='" + ds.Columns["Oberfläche"] + "',Endanschlag='"+ ds.Columns["Endanschlag"] + "';";
                ds = dgv.DataSource as DataTable;
                using (SqlConnection sqlCon = new SqlConnection(GlobalVariables.connectionKapazität))
                {
                    sqlCon.Open();
                    SqlCommand cmdUpdate = new SqlCommand(queryUpdate, sqlCon);
                    cmdUpdate.ExecuteNonQuery();
                    //sqlGridDa = new SqlDataAdapter();
                    //sqlGridDa.SelectCommand = new SqlCommand(queryUpdate, sqlCon);
                    //SqlCommandBuilder cmdUpdate = new SqlCommandBuilder(sqlGridDa);
                    //sqlCon.Open();
                    //cmdUpdate.GetUpdateCommand();
                    //sqlGridDa.Update(ds);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Fehler", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}