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 Transportation
{
    public partial class MainMenu : Form
    {
        SqlCommand query = null;
        SqlDataAdapter dataAdapter = null;
        DataSet dataSet = null;
        static public string vehicleIndex;
        static public string driverIndex;
        static public string executorIndex;
        static public string logisticianIndex;
        static public string requestIndex;
        static public double sort;
        public MainMenu()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
          
            dbhelper.openConnection(ref dbhelper.sqlConnection);

            dataAdapter = new SqlDataAdapter("select code_vehicle as 'Код автомобиля', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', brand_vehicle as 'Марка автомобиля', model_vehicle as 'Модель автомобиля', number_vehicle as 'Гос. номер автомобиля', name_category as 'Категория прицепа', volume_body as 'Тоннаж', length_body as 'Длина автомобиля', width_body as 'Ширина автомобиля', height_body as 'Высота автомобиля' from vehicles, executors, category_trailer where vehicles.code_executor = executors.code_executor and vehicles.code_category = category_trailer.code_category", dbhelper.sqlConnection);
            dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            VehiclesDGV.DataSource = dataSet.Tables[0];

            dataAdapter = new SqlDataAdapter("select code_driver as 'Код водителя', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', surname_driver as 'Фамилия водителя', name_driver as 'Имя водителя', midname_driver as 'Отчество водителя', number_passport_driver as 'Серия-номер паспорта', place_passport_driver as 'Место выдачи паспорта', date_passport_driver as 'Дата выдачи паспорта', code_passport_driver as 'Код подразделения', number_driverlicense as 'Серия-номер вод. удостоверения', telephone_driver as 'Телефон водителя' from executors, drivers where drivers.code_executor = executors.code_executor", dbhelper.sqlConnection);
            dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            DriversDGV.DataSource = dataSet.Tables[0];

            dataAdapter = new SqlDataAdapter("select code_executor as 'Код исполнителя', surname_executor as 'Фамилия исполнителя', name_executor as 'Имя исполнителя', midname_executor as 'Отчество исполнителя' from Executors", dbhelper.sqlConnection);
            dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            ExecutorsDGV.DataSource = dataSet.Tables[0];

            dataAdapter = new SqlDataAdapter("select code_logistician as 'Код логиста', surname_logistician as 'Фамилия логиста', name_logistician as 'Имя логиста', midname_logistician as 'Отчество логиста', number_passport_logistician as 'Серия-номер паспорта', place_passport_logistician as 'Место выдачи паспорта', date_passport_logistician as 'Дата выдачи паспорта', code_passport_logistician as 'Код подразделения', telephone_logistician as 'Телефон логиста' from Logisticians", dbhelper.sqlConnection);
            dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            LogisticiansDGV.DataSource = dataSet.Tables[0];

            dataAdapter = new SqlDataAdapter("select code_request as 'Код заявки', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', (drivers.surname_driver + ' ' + drivers.name_driver + ' ' + drivers.midname_driver) as 'ФИО водителя', (vehicles.brand_vehicle + ' ' + vehicles.model_vehicle + ' ' + vehicles.number_vehicle) as 'Автомобиль', (logisticians.surname_logistician + ' ' + logisticians.name_logistician + ' ' + logisticians.midname_logistician) as 'ФИО логиста', date_loading as 'Дата загрузки', place_loading as 'Место загрузки', fullname_shipper as 'ФИО грузоотправителя', telephone_shipper as 'Телефон грузоотправителя', name_cargo as 'Наименования груза', transportation_conditions as 'Условия транспортировки', date_unloading as 'Дата разгрузки', place_unloading as 'Место разгрузки', fullname_consignee as 'ФИО грузополучателя', route_town_request as 'Маршрут (города)', route_mileage_request as 'Маршрут (километраж)', transportation_format as 'Формат транспортировки', payment_request as 'Оплата заявки', payment_conditions_request as 'Условия оплаты' from Requests, executors, drivers, vehicles, logisticians where requests.code_executor = executors.code_executor and requests.code_driver = drivers.code_driver and requests.code_vehicle = vehicles.code_vehicle and requests.code_logistician = logisticians.code_logistician", dbhelper.sqlConnection);
            dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            RequestsDGV.DataSource = dataSet.Tables[0];
        }

        private void AddVehicleTSMI_Click(object sender, EventArgs e)
        {
            AddVehicle add = new AddVehicle();
            add.Show();

            add.FormClosing += (obj, args) =>
              {
                  dataAdapter = new SqlDataAdapter("select code_vehicle as 'Код автомобиля', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', brand_vehicle as 'Марка автомобиля', model_vehicle as 'Модель автомобиля', number_vehicle as 'Гос. номер автомобиля', name_category as 'Категория прицепа', volume_body as 'Тоннаж', length_body as 'Длина автомобиля', width_body as 'Ширина автомобиля', height_body as 'Высота автомобиля' from vehicles, executors, category_trailer where vehicles.code_executor = executors.code_executor and vehicles.code_category = category_trailer.code_category", dbhelper.sqlConnection);
                  dataSet = new DataSet();
                  dataAdapter.Fill(dataSet);
                  VehiclesDGV.DataSource = dataSet.Tables[0];
              };
        }

        private void ChangeVehicleTSMI_Click(object sender, EventArgs e)
        {
            vehicleIndex = VehiclesDGV.Rows[VehiclesDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
            UpdateVehicle update = new UpdateVehicle();
            update.Show();

            update.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_vehicle as 'Код автомобиля', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', brand_vehicle as 'Марка автомобиля', model_vehicle as 'Модель автомобиля', number_vehicle as 'Гос. номер автомобиля', name_category as 'Категория прицепа', volume_body as 'Тоннаж', length_body as 'Длина автомобиля', width_body as 'Ширина автомобиля', height_body as 'Высота автомобиля' from vehicles, executors, category_trailer where vehicles.code_executor = executors.code_executor and vehicles.code_category = category_trailer.code_category", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                VehiclesDGV.DataSource = dataSet.Tables[0];
            };
        }

        private void DropVehicleTSMI_Click(object sender, EventArgs e)
        {
            var res = MessageBox.Show("Подтвердите удаление данных", "Подтверждение", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (res == DialogResult.Yes)
            {
                vehicleIndex = VehiclesDGV.Rows[VehiclesDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
                query = new SqlCommand($"delete from vehicles where code_vehicle like '{vehicleIndex}'", dbhelper.sqlConnection);
                query.ExecuteNonQuery();

                dataAdapter = new SqlDataAdapter("select code_vehicle as 'Код автомобиля', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', brand_vehicle as 'Марка автомобиля', model_vehicle as 'Модель автомобиля', number_vehicle as 'Гос. номер автомобиля', name_category as 'Категория прицепа', volume_body as 'Тоннаж', length_body as 'Длина автомобиля', width_body as 'Ширина автомобиля', height_body as 'Высота автомобиля' from vehicles, executors, category_trailer where vehicles.code_executor = executors.code_executor and vehicles.code_category = category_trailer.code_category", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                VehiclesDGV.DataSource = dataSet.Tables[0];
            }

        }

        private void AddDriverTSMI_Click(object sender, EventArgs e)
        {
            AddDriver add = new AddDriver();
            add.Show();

            add.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_driver as 'Код водителя', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', surname_driver as 'Фамилия водителя', name_driver as 'Имя водителя', midname_driver as 'Отчество водителя', number_passport_driver as 'Серия-номер паспорта', place_passport_driver as 'Место выдачи паспорта', date_passport_driver as 'Дата выдачи паспорта', code_passport_driver as 'Код подразделения', number_driverlicense as 'Серия-номер вод. удостоверения', telephone_driver as 'Телефон водителя' from executors, drivers where drivers.code_executor = executors.code_executor", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                DriversDGV.DataSource = dataSet.Tables[0];
            };
        }

        private void ChangeDriverTSMI_Click(object sender, EventArgs e)
        {
            driverIndex = DriversDGV.Rows[DriversDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
            UpdateDrivers update = new UpdateDrivers();
            update.Show();

            update.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_driver as 'Код водителя', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', surname_driver as 'Фамилия водителя', name_driver as 'Имя водителя', midname_driver as 'Отчество водителя', number_passport_driver as 'Серия-номер паспорта', place_passport_driver as 'Место выдачи паспорта', date_passport_driver as 'Дата выдачи паспорта', code_passport_driver as 'Код подразделения', number_driverlicense as 'Серия-номер вод. удостоверения', telephone_driver as 'Телефон водителя' from executors, drivers where drivers.code_executor = executors.code_executor", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                DriversDGV.DataSource = dataSet.Tables[0];
            };
        }

        private void DropDriverTSMI_Click(object sender, EventArgs e)
        {
            var res = MessageBox.Show("Подтвердите удаление данных", "Подтверждение", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (res == DialogResult.Yes)
            {
                driverIndex = DriversDGV.Rows[DriversDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
                query = new SqlCommand($"delete from drivers where code_driver like '{driverIndex}'", dbhelper.sqlConnection);
                query.ExecuteNonQuery();

                dataAdapter = new SqlDataAdapter("select code_driver as 'Код водителя', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', surname_driver as 'Фамилия водителя', name_driver as 'Имя водителя', midname_driver as 'Отчество водителя', number_passport_driver as 'Серия-номер паспорта', place_passport_driver as 'Место выдачи паспорта', date_passport_driver as 'Дата выдачи паспорта', code_passport_driver as 'Код подразделения', number_driverlicense as 'Серия-номер вод. удостоверения', telephone_driver as 'Телефон водителя' from executors, drivers where drivers.code_executor = executors.code_executor", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                DriversDGV.DataSource = dataSet.Tables[0];
            }
        }

        private void AddExecutorTSMI_Click(object sender, EventArgs e)
        {
            AddExecutor add = new AddExecutor();
            add.Show();

            add.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_executor as 'Код исполнителя', surname_executor as 'Фамилия исполнителя', name_executor as 'Имя исполнителя', midname_executor as 'Отчество исполнителя' from Executors", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                ExecutorsDGV.DataSource = dataSet.Tables[0];
            };
        }

        private void UpdateExecutorTSMI_Click(object sender, EventArgs e)
        {
            executorIndex = ExecutorsDGV.Rows[ExecutorsDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
            UpdateExecutor update = new UpdateExecutor();
            update.Show();

            update.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_executor as 'Код исполнителя', surname_executor as 'Фамилия исполнителя', name_executor as 'Имя исполнителя', midname_executor as 'Отчество исполнителя' from Executors", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                ExecutorsDGV.DataSource = dataSet.Tables[0];
            };
        }

        private void DropExecutorTSMI_Click(object sender, EventArgs e)
        {
            var res = MessageBox.Show("Подтвердите удаление данных", "Подтверждение", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (res == DialogResult.Yes)
            {
                executorIndex = ExecutorsDGV.Rows[ExecutorsDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
                query = new SqlCommand($"delete from executors where code_executor like '{executorIndex}'", dbhelper.sqlConnection);
                query.ExecuteNonQuery();

                dataAdapter = new SqlDataAdapter("select code_executor as 'Код исполнителя', surname_executor as 'Фамилия исполнителя', name_executor as 'Имя исполнителя', midname_executor as 'Отчество исполнителя' from Executors", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                ExecutorsDGV.DataSource = dataSet.Tables[0];
            }
        }

        private void AddLogisticianTSMI_Click(object sender, EventArgs e)
        {
            AddLogistician add = new AddLogistician();
            add.Show();

            add.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_logistician as 'Код логиста', surname_logistician as 'Фамилия логиста', name_logistician as 'Имя логиста', midname_logistician as 'Отчество логиста', number_passport_logistician as 'Серия-номер паспорта', place_passport_logistician as 'Место выдачи паспорта', date_passport_logistician as 'Дата выдачи паспорта', code_passport_logistician as 'Код подразделения', telephone_logistician as 'Телефон логиста' from Logisticians", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                LogisticiansDGV.DataSource = dataSet.Tables[0];
            };
        }

        private void UpdateLogisticianTSMI_Click(object sender, EventArgs e)
        {
            logisticianIndex = LogisticiansDGV.Rows[LogisticiansDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
            UpdateLogistician update = new UpdateLogistician();
            update.Show();

            update.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_logistician as 'Код логиста', surname_logistician as 'Фамилия логиста', name_logistician as 'Имя логиста', midname_logistician as 'Отчество логиста', number_passport_logistician as 'Серия-номер паспорта', place_passport_logistician as 'Место выдачи паспорта', date_passport_logistician as 'Дата выдачи паспорта', code_passport_logistician as 'Код подразделения', telephone_logistician as 'Телефон логиста' from Logisticians", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                LogisticiansDGV.DataSource = dataSet.Tables[0];
            };
        }

        private void DropLogisticianTSMI_Click(object sender, EventArgs e)
        {
            var res = MessageBox.Show("Подтвердите удаление данных", "Подтверждение", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (res == DialogResult.Yes)
            {
                logisticianIndex = LogisticiansDGV.Rows[LogisticiansDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
                query = new SqlCommand($"delete from logisticians where code_logistician like '{logisticianIndex}'", dbhelper.sqlConnection);
                query.ExecuteNonQuery();

                dataAdapter = new SqlDataAdapter("select code_logistician as 'Код логиста', surname_logistician as 'Фамилия логиста', name_logistician as 'Имя логиста', midname_logistician as 'Отчество логиста', number_passport_logistician as 'Серия-номер паспорта', place_passport_logistician as 'Место выдачи паспорта', date_passport_logistician as 'Дата выдачи паспорта', code_passport_logistician as 'Код подразделения', telephone_logistician as 'Телефон логиста' from Logisticians", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                LogisticiansDGV.DataSource = dataSet.Tables[0];
            }
        }

        private void check_Click(object sender, EventArgs e)
        {
            sort = Convert.ToDouble(volume.Value);

            SortingVehicle add = new SortingVehicle();
            add.Show();
        }

        private void AddRequestTSMI_Click(object sender, EventArgs e)
        {
            AddRequest add = new AddRequest();
            add.Show();

            add.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_request as 'Код заявки', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', (drivers.surname_driver + ' ' + drivers.name_driver + ' ' + drivers.midname_driver) as 'ФИО водителя', (vehicles.brand_vehicle + ' ' + vehicles.model_vehicle + ' ' + vehicles.number_vehicle) as 'Автомобиль', (logisticians.surname_logistician + ' ' + logisticians.name_logistician + ' ' + logisticians.midname_logistician) as 'ФИО логиста', date_loading as 'Дата загрузки', place_loading as 'Место загрузки', fullname_shipper as 'ФИО грузоотправителя', telephone_shipper as 'Телефон грузоотправителя', name_cargo as 'Наименования груза', transportation_conditions as 'Условия транспортировки', date_unloading as 'Дата разгрузки', place_unloading as 'Место разгрузки', fullname_consignee as 'ФИО грузополучателя', route_town_request as 'Маршрут (города)', route_mileage_request as 'Маршрут (километраж)', transportation_format as 'Формат транспортировки', payment_request as 'Оплата заявки', payment_conditions_request as 'Условия оплаты' from Requests, executors, drivers, vehicles, logisticians where requests.code_executor = executors.code_executor and requests.code_driver = drivers.code_driver and requests.code_vehicle = vehicles.code_vehicle and requests.code_logistician = logisticians.code_logistician", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                RequestsDGV.DataSource = dataSet.Tables[0];
            };
        }


        private void UpdateRequestTSMI_Click(object sender, EventArgs e)
        {
            requestIndex = RequestsDGV.Rows[RequestsDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
            UpdateRequest update = new UpdateRequest();
            update.Show();

            update.FormClosing += (obj, args) =>
            {
                dataAdapter = new SqlDataAdapter("select code_request as 'Код заявки', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', (drivers.surname_driver + ' ' + drivers.name_driver + ' ' + drivers.midname_driver) as 'ФИО водителя', (vehicles.brand_vehicle + ' ' + vehicles.model_vehicle + ' ' + vehicles.number_vehicle) as 'Автомобиль', (logisticians.surname_logistician + ' ' + logisticians.name_logistician + ' ' + logisticians.midname_logistician) as 'ФИО логиста', date_loading as 'Дата загрузки', place_loading as 'Место загрузки', fullname_shipper as 'ФИО грузоотправителя', telephone_shipper as 'Телефон грузоотправителя', name_cargo as 'Наименования груза', transportation_conditions as 'Условия транспортировки', date_unloading as 'Дата разгрузки', place_unloading as 'Место разгрузки', fullname_consignee as 'ФИО грузополучателя', route_town_request as 'Маршрут (города)', route_mileage_request as 'Маршрут (километраж)', transportation_format as 'Формат транспортировки', payment_request as 'Оплата заявки', payment_conditions_request as 'Условия оплаты' from Requests, executors, drivers, vehicles, logisticians where requests.code_executor = executors.code_executor and requests.code_driver = drivers.code_driver and requests.code_vehicle = vehicles.code_vehicle and requests.code_logistician = logisticians.code_logistician", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                RequestsDGV.DataSource = dataSet.Tables[0];
            };
        }

        private void DropRequestTSMI_Click(object sender, EventArgs e)
        {
            var res = MessageBox.Show("Подтвердите удаление данных", "Подтверждение", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (res == DialogResult.Yes)
            {
                requestIndex = RequestsDGV.Rows[RequestsDGV.CurrentCell.RowIndex].Cells[0].Value.ToString();
                query = new SqlCommand($"delete from requests where code_request like '{requestIndex}'", dbhelper.sqlConnection);
                query.ExecuteNonQuery();

                dataAdapter = new SqlDataAdapter("select code_request as 'Код заявки', (executors.surname_executor + ' ' + executors.name_executor + ' ' + executors.midname_executor) as 'ФИО исполнителя', (drivers.surname_driver + ' ' + drivers.name_driver + ' ' + drivers.midname_driver) as 'ФИО водителя', (vehicles.brand_vehicle + ' ' + vehicles.model_vehicle + ' ' + vehicles.number_vehicle) as 'Автомобиль', (logisticians.surname_logistician + ' ' + logisticians.name_logistician + ' ' + logisticians.midname_logistician) as 'ФИО логиста', date_loading as 'Дата загрузки', place_loading as 'Место загрузки', fullname_shipper as 'ФИО грузоотправителя', telephone_shipper as 'Телефон грузоотправителя', name_cargo as 'Наименования груза', transportation_conditions as 'Условия транспортировки', date_unloading as 'Дата разгрузки', place_unloading as 'Место разгрузки', fullname_consignee as 'ФИО грузополучателя', route_town_request as 'Маршрут (города)', route_mileage_request as 'Маршрут (километраж)', transportation_format as 'Формат транспортировки', payment_request as 'Оплата заявки', payment_conditions_request as 'Условия оплаты' from Requests, executors, drivers, vehicles, logisticians where requests.code_executor = executors.code_executor and requests.code_driver = drivers.code_driver and requests.code_vehicle = vehicles.code_vehicle and requests.code_logistician = logisticians.code_logistician", dbhelper.sqlConnection);
                dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                RequestsDGV.DataSource = dataSet.Tables[0];
            }
        }
    }
    
}