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 Дол_Восток
{
    public partial class changeSotrudnic : Form
    {
        SqlCommand query = null;

        public changeSotrudnic()
        {
            InitializeComponent();
        }

        private void changeSotrudnic_Load(object sender, EventArgs e)
        {
            try
            {
                query = new SqlCommand($"select dolgnost from dolgnost", db_helper.sqlConnection);
                SqlDataReader reader = query.ExecuteReader();
                while (reader.Read())
                {
                    cb_dolgnost.Items.Add(reader.GetString(0));
                }
                reader.Close();

                query = new SqlCommand($"select surname from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                tb_surname.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select [name] from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                tb_name.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select patronymic from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                tb_patronymic.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select telephone from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_telephone.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select date_birth from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                dtp_dateBirth.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select adress from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                tb_adress.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select family_status from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                cb_familyStatus.SelectedItem = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select obrazovanie from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                cb_obrazovanie.SelectedItem = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select kem_vudan from sotrudnic join passport on passport.kod_passport = sotrudnic.kod_passport where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                tb_kemVudan.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select date_vudach from sotrudnic join passport on passport.kod_passport = sotrudnic.kod_passport where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                dtp_dateVudach.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select seria from sotrudnic join passport on passport.kod_passport = sotrudnic.kod_passport where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_seria.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select nomer from sotrudnic join passport on passport.kod_passport = sotrudnic.kod_passport where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_nomer.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select inn from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_INN.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select snils from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_snils.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select num_med_book from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                nud_numMedBook.Value = Convert.ToInt32(query.ExecuteScalar());

                query = new SqlCommand($"select dolgnost from sotrudnic join dolgnost on dolgnost.kod_dolgnost = sotrudnic.kod_dolgnost where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                cb_dolgnost.SelectedItem = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select salary from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                nud_salary.Value = Convert.ToInt32(query.ExecuteScalar());

                query = new SqlCommand($"select kol_vo_smen from sotrudnic join experience on experience.kod_experince = sotrudnic.kod_experience where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                nud_kolSmen.Value = Convert.ToInt32(query.ExecuteScalar());

                query = new SqlCommand($"select num_sertificat from sotrudnic join experience on experience.kod_experince = sotrudnic.kod_experience where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_numSertificat.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select work_place from sotrudnic join experience on experience.kod_experince = sotrudnic.kod_experience where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                tb_workPlace.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select student_otryad from sotrudnic join experience on experience.kod_experince = sotrudnic.kod_experience where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                tb_studentOtryad.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select status_otryad from sotrudnic join experience on experience.kod_experince = sotrudnic.kod_experience where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                tb_statusOtryad.Text = query.ExecuteScalar().ToString();
            }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void bt_change_Click(object sender, EventArgs e)
        {
            try
            {
                query = new SqlCommand($"select kod_passport from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                string kodPassport = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select kod_experience from sotrudnic where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                string kodExperience = query.ExecuteScalar().ToString();

                DateTime date1 = DateTime.Parse(dtp_dateVudach.Text);
                query = new SqlCommand($"update passport set kem_vudan = @kem_vudan, date_vudach = @date_vudach, seria = @seria, nomer = @nomer where kod_passport like '{kodPassport}'", db_helper.sqlConnection);
                query.Parameters.AddWithValue($"kem_vudan", tb_kemVudan.Text);
                query.Parameters.AddWithValue($"date_vudach", $"{date1.Day}.{date1.Month}.{date1.Year}");
                query.Parameters.AddWithValue($"seria", mtb_seria.Text);
                query.Parameters.AddWithValue($"nomer", mtb_nomer.Text);
                query.ExecuteNonQuery();

                query = new SqlCommand($"update experience set kol_vo_smen = @kol_vo_smen, num_sertificat = @num_sertificat, work_place = @work_place, student_otryad = @student_otryad, status_otryad = @status_otryad where kod_experince like '{kodExperience}'", db_helper.sqlConnection);
                query.Parameters.AddWithValue($"kol_vo_smen", nud_kolSmen.Value);
                query.Parameters.AddWithValue($"num_sertificat", mtb_numSertificat.Text);
                query.Parameters.AddWithValue($"work_place", tb_workPlace.Text);
                query.Parameters.AddWithValue($"student_otryad", tb_studentOtryad.Text);
                query.Parameters.AddWithValue($"status_otryad", tb_statusOtryad.Text);
                query.ExecuteNonQuery();

                query = new SqlCommand($"select kod_dolgnost from dolgnost where dolgnost like '{cb_dolgnost.SelectedItem}'", db_helper.sqlConnection);
                string kodDolgnost = query.ExecuteScalar().ToString();

                date1 = DateTime.Parse(dtp_dateBirth.Text);
                query = new SqlCommand($"update sotrudnic set surname = @surname, [name] = @name, patronymic = @patronymic, telephone = @telephone, date_birth = @date_birth, adress = @adress, family_status = @family_status, obrazovanie = @obrazovanie, inn = @inn, snils = @snils, num_med_book = @num_med_book, kod_dolgnost = @kod_dolgnost, salary = @salary where kod_sotrudnic like '{GLavn.row}'", db_helper.sqlConnection);
                query.Parameters.AddWithValue($"surname", tb_surname.Text);
                query.Parameters.AddWithValue($"name", tb_name.Text);
                query.Parameters.AddWithValue($"patronymic", tb_patronymic.Text);
                query.Parameters.AddWithValue($"telephone", mtb_telephone.Text);
                query.Parameters.AddWithValue($"date_birth", $"{date1.Day}.{date1.Month}.{date1.Year}");
                query.Parameters.AddWithValue($"adress", tb_adress.Text);
                query.Parameters.AddWithValue($"family_status", cb_familyStatus.SelectedItem);
                query.Parameters.AddWithValue($"obrazovanie", cb_obrazovanie.SelectedItem);
                query.Parameters.AddWithValue($"inn", mtb_INN.Text);
                query.Parameters.AddWithValue($"snils", mtb_snils.Text);
                query.Parameters.AddWithValue($"num_med_book", nud_numMedBook.Value);
                query.Parameters.AddWithValue($"kod_dolgnost", kodDolgnost);
                query.Parameters.AddWithValue($"salary", nud_salary.Value);
                if (query.ExecuteNonQuery().ToString().Equals("1"))
                {
                    MessageBox.Show("Данные успешно изменены!", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.Close();
                }
                else
                {
                    MessageBox.Show("", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}