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

namespace Sprawko2_DB
{
    public partial class DB2 : Form
    {
        public DB2()
        {
            InitializeComponent();
        }


        #region GLOBALVARS
        int ActiveButton = 0;

        static LinqDataContext dc = new LinqDataContext();
        Table<Pracownicy> table = dc.GetTable<Pracownicy>();
        #endregion

        #region BASICFUNCs

        private void DisableButtons(bool onoff)
        {
            foreach (Control x in WorkersGroupBox.Controls)
            {
                if (x is TextBox)
                {
                    x.Enabled = onoff;
                }

            }

            StateBox.Enabled = onoff;
        }

        void Backlight(int temp, bool type)
        {
            int id;
            if (type)
                id = ReadID(temp);
            else
                id = temp;

            if (id > 0)
            {
                DGV.Rows[id].Selected = true;
                DGV.CurrentCell = DGV.Rows[id].Cells[0];
            }

        }

        int ReadID(int id)
        {
            int resultId = -1;

            for (int i = 0; i < DGV.Rows.Count - 1; i++)
            {
                if (Convert.ToInt32(DGV.Rows[i].Cells[0].Value.ToString()) == id)
                    resultId = i;
            }

            return resultId;
        }

        void WriteToBoxes(int id)
        {
            StateBox.Text = DGV.Rows[id].Cells[1].Value.ToString();
            NameBox.Text = DGV.Rows[id].Cells[2].Value.ToString();
            SurnameBox.Text = DGV.Rows[id].Cells[3].Value.ToString();
            AddressBox.Text = DGV.Rows[id].Cells[4].Value.ToString();
            PhoneBox.Text = DGV.Rows[id].Cells[5].Value.ToString();
        }

        void ClearButtons()
        {
            AddButton.BackColor = Color.White;
            EditButton.BackColor = Color.White;
            DeleteButton.BackColor = Color.White;

        }

        void ClearBoxes()
        {
            foreach (Control x in WorkersGroupBox.Controls)
            {
                if (x is TextBox)
                {
                    x.Text = String.Empty;
                }

            }

            StateBox.Text = String.Empty;
        }

        bool CheckState()
        {
            bool result = false;

            for (int i = 0; i < StateBox.Items.Count; i++)
                if (StateBox.Text == StateBox.GetItemText(StateBox.Items[i]))
                    result = true;

            return result;
        }
        #endregion

        #region CRUDFUNCs
        bool AddWorker()
        {

            string[] worker = new string[5];

            try
            {
                worker[0] = StateBox.Text;
                worker[1] = NameBox.Text;
                worker[2] = SurnameBox.Text;
                worker[3] = AddressBox.Text;
                worker[4] = PhoneBox.Text;


                if (StateBox.Text.Length == 0)
                    worker[0] = null;

                if (NameBox.Text.Length == 0)
                    worker[1] = null;

                if (SurnameBox.Text.Length == 0)
                    worker[2] = null;

                if (AddressBox.Text.Length == 0)
                    worker[3] = null;

                if (PhoneBox.Text.Length == 0)
                    worker[4] = null;

               var prac = new Pracownicy
                {
                    Stanowisko = worker[0],
                    Imie = worker[1],
                    Nazwisko = worker[2],
                    Adres = worker[3],
                    Telefon = worker[4]
                };


                dc.GetTable<Pracownicy>().InsertOnSubmit(prac);
                dc.GetTable<Pracownicy>().Context.SubmitChanges();
                return true;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                ExcepitonHandler(ex.Number);
                return false;
            }







        }

        bool UpdateWorker(int ID)
        {

            bool inside = false;
            try
            {

                var query =
                    from prac in dc.GetTable<Pracownicy>()
                    where prac.IdPracownika == ID
                    select prac;

                foreach (Pracownicy worker in query)
                {
                    inside = true;

                        if (NameBox.Text.Length > 0)
                            worker.Imie = NameBox.Text;
                        else
                            worker.Imie = null;

                        if (SurnameBox.Text.Length > 0)
                            worker.Nazwisko = SurnameBox.Text;
                        else
                            worker.Nazwisko = null;

                        if (AddressBox.Text.Length > 0)
                            worker.Adres = AddressBox.Text;
                        else
                            worker.Adres = null;

                        if (PhoneBox.Text.Length > 0)
                            worker.Telefon = PhoneBox.Text;
                        else
                            worker.Telefon = null;

                        if (StateBox.Text.Length > 0)
                            worker.Stanowisko = StateBox.Text;
                        else
                            worker.Stanowisko = null;


                }
                if (!inside) throw new Exception("0");
                dc.GetTable<Pracownicy>().Context.SubmitChanges();
                return true;

            }
            catch (SqlException ex)
            {
                ExcepitonHandler(ex.Number);
                return false;
            }
            catch (Exception ex)
            {
                ExcepitonHandler(Convert.ToInt32(ex.Message));
                return false;
            }


        }

        bool DeleteWorker(int ID)
        {
            try
            {
            var delpatfile = dc.GetTable<Pracownicy>().SingleOrDefault(p => p.IdPracownika == ID);

            dc.GetTable<Pracownicy>().DeleteOnSubmit(delpatfile);


            dc.GetTable<Pracownicy>().Context.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                ExcepitonHandler(0);
                return false;
            }

        }


        bool AddWorkerProc()
        {
            string[] worker = new string[5];

            try
            {
                worker[0] = StateBox.Text;
                worker[1] = NameBox.Text;
                worker[2] = SurnameBox.Text;
                worker[3] = AddressBox.Text;
                worker[4] = PhoneBox.Text;


                if (StateBox.Text.Length == 0)
                    worker[0] = null;

                if (NameBox.Text.Length == 0)
                    worker[1] = null;

                if (SurnameBox.Text.Length == 0)
                    worker[2] = null;

                if (AddressBox.Text.Length == 0)
                    worker[3] = null;

                if (PhoneBox.Text.Length == 0)
                    worker[4] = null;

                dc.InsertPracownik(null, worker[0], worker[1], worker[2], worker[3], worker[5]);
                return true;
            }
            catch (SqlException ex)
            {
                ExcepitonHandler(ex.Number);
                return false;
            }

        }

        bool UpdateWorkerProc(int ID)
        {
            try
            {
                //dc.UpdatePracownik(ID, StateBox.Text, NameBox.Text, SurnameBox.Text, AddressBox.Text, PhoneBox.Text);
                return true;
            }
            catch(SqlException ex)
            {
                ExcepitonHandler(ex.Number);
                return false;
            }

        }

        bool DeleteWorkerProc(int ID)
        {
            try
            {
                //dc.DeletePracownik(ID);
                return true;
            }
            catch (SqlException ex)
            {
                ExcepitonHandler(ex.Number);
                return false;
            }
        }

        void SelectTable()
        {
            dc = new LinqDataContext();
            DGV.DataSource = dc.GetTable<Pracownicy>();
            DGVV.DataSource = dc.GetTable<prac_adre>();

        }

        void ClearCounter()
        {
            dc.ExecuteCommand("DBCC CHECKIDENT('Pracownicy', RESEED, 0);");
        }

        #endregion


        void ApplyAction(bool linq)
        {
            if (linq)
            {
                switch (ActiveButton)
                {
                    case 0: { if (AddWorker()) { SelectTable(); Backlight(DGV.Rows.Count - 2, false); } break; }
                    case 1: { if (UpdateWorker(Convert.ToInt32(IDBox.Text))) { SelectTable(); Backlight(Convert.ToInt32(IDBox.Text), true); } break; }
                    case 2: { if (DeleteWorker(Convert.ToInt32(IDBox.Text))) SelectTable(); break; }
                }
                ClearBoxes();
            }
            else
            {
                switch (ActiveButton)
                {
                    case 0: { if (AddWorkerProc()) { SelectTable(); Backlight(DGV.Rows.Count - 2, false); } break; }
                    case 1: { if (UpdateWorkerProc(Convert.ToInt32(IDBox.Text))) { SelectTable(); Backlight(Convert.ToInt32(IDBox.Text), true); } break; }
                    case 2: { if (DeleteWorkerProc(Convert.ToInt32(IDBox.Text))) SelectTable(); break; }
                }
                ClearBoxes();
            }



        }

        void ExcepitonHandler(int exid)
        {
            string errorText = String.Empty;
            switch (exid)
            {

                case 515: { errorText = "Nieprawidłowe dane. Żadne pole nie może być puste. "; break; }
                case 547:
                    {
                        if (CheckState())
                            errorText = "Nieprawidłowa długość pola Telefon";
                        else
                            errorText = "Nieprawidłowa zawartość pola Stanowisko";
                        break;
                    }
                case 0: { errorText = "Brak rekordu o podanym ID w bazie"; break; }
                case 5132: { errorText = ""; break; }
                case 5142: { errorText = ""; break; }
                case 5152: { errorText = ""; break; }
                case 5162: { errorText = ""; break; }

            }
            MessageBox.Show(exid.ToString() + " :: " + errorText);
        }



        #region EVENTS
        private void DB2_Load(object sender, EventArgs e)
        {
            AddButton.PerformClick();
            SelectTable();

        }

        private void AddButton_Click(object sender, EventArgs e)
        {
            DisableButtons(true);
            //sClearCounter();
            ActiveButton = 0;
            ClearButtons();
            AddButton.BackColor = Color.DodgerBlue;
            IDBox.Text = String.Empty;
            IDBox.Enabled = false;
        }

        private void EditButton_Click(object sender, EventArgs e)
        {
            ClearBoxes();
            DisableButtons(true);
            IDBox.Enabled = true;
            ActiveButton = 1;
            ClearButtons();
            EditButton.BackColor = Color.DodgerBlue;
        }

        private void DeleteButton_Click(object sender, EventArgs e)
        {
            ClearBoxes();
            DisableButtons(false);
            StateBox.Enabled = false;
            IDBox.Enabled = true;
            ActiveButton = 2;
            ClearButtons();
            DeleteButton.BackColor = Color.DodgerBlue;
        }

        private void IDBox_TextChanged(object sender, EventArgs e)
        {

            try
            {
                Backlight(Convert.ToInt32(IDBox.Text),true);
                var idData = ReadID(Convert.ToInt32(IDBox.Text));
                WriteToBoxes(idData);

            }
            catch
            {

            }
        }

        private void ApplyProcButton_Click(object sender, EventArgs e)
        {
            ApplyAction(false);
        }

        private void ApplyButton_Click(object sender, EventArgs e)
        {
            ApplyAction(true);
        }

        #endregion


    }


}