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 UpdateRequest : Form
    {
        SqlCommand query = null;
        SqlDataReader reader = null;
        public UpdateRequest()
        {
            InitializeComponent();
        }

        private void UpdateRequest_Load(object sender, EventArgs e)
        {
            query = new SqlCommand("select surname_executor + ' ' + name_executor + ' ' + midname_executor from Executors", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            while (reader.Read())
            {
                ExecutorsCB.Items.Add(reader.GetString(0));
            }
            reader.Close();

            query = new SqlCommand("select surname_driver + ' ' + name_driver + ' ' + midname_driver from Drivers", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            while (reader.Read())
            {
                DriversCB.Items.Add(reader.GetString(0));
            }
            reader.Close();

            query = new SqlCommand("select brand_vehicle + ' ' + model_vehicle + ' ' + number_vehicle from Vehicles", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            while (reader.Read())
            {
                VehiclesCB.Items.Add(reader.GetString(0));
            }
            reader.Close();

            query = new SqlCommand("select surname_logistician + ' ' + name_logistician + ' ' + midname_logistician from Logisticians", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            while (reader.Read())
            {
                LogisticiansCB.Items.Add(reader.GetString(0));
            }
            reader.Close();

            query = new SqlCommand($"select code_executor, code_driver, code_vehicle, date_loading, place_loading, fullname_shipper, telephone_shipper, name_cargo, transportation_conditions, date_unloading, place_unloading, fullname_consignee, telephone_consignee, route_town_request, route_mileage_request, transportation_format, payment_request, payment_conditions_request, code_logistician from requests where code_request like '{MainMenu.requestIndex}'", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            reader.Read();
            int codeExecutor = reader.GetInt32(0);
            int codeDriver = reader.GetInt32(1);
            int codeVehicle = reader.GetInt32(2);
            DateLoadingDTP.Value = reader.GetDateTime(3);
            PlaceLoadingRTB.Text = reader.GetString(4);
            NameShipperTB.Text = reader.GetString(5);
            TelephoneShipperMTB.Text = reader.GetString(6);
            NameCargoRTB.Text = reader.GetString(7);
            TransportationConditionsRTB.Text = reader.GetString(8);
            DateUnloadingDTP.Value = reader.GetDateTime(9);
            PlaceUnloadingRTP.Text = reader.GetString(10);
            NameConsigneeTB.Text = reader.GetString(11);
            TelephoneConsigneeMTB.Text = reader.GetString(12);
            RoutePlaceTB.Text = reader.GetString(13);
            RouteMileageTB.Text = reader.GetString(14);
            TransportationFormatDTP.Text = reader.GetString(15);
            PaymentTB.Text = reader.GetString(16);
            PaymentConditionsTB.Text = reader.GetString(17);
            int codeLogistician = reader.GetInt32(18);

            reader.Close();
            query = new SqlCommand($"select (surname_executor + ' ' + name_executor + ' ' + midname_executor) from Executors where code_executor like '{codeExecutor}'", dbhelper.sqlConnection);
            ExecutorsCB.SelectedItem = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select (surname_driver + ' ' + name_driver + ' ' + midname_driver) from drivers where code_driver like '{codeDriver}'", dbhelper.sqlConnection);
            DriversCB.SelectedItem = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select (brand_vehicle + ' ' + model_vehicle + ' ' + number_vehicle) from vehicles where code_vehicle like '{codeVehicle}'", dbhelper.sqlConnection);
            VehiclesCB.SelectedItem = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select (surname_logistician + ' ' + name_logistician + ' ' + midname_logistician) from logisticians where code_logistician like '{codeLogistician}'", dbhelper.sqlConnection);
            LogisticiansCB.SelectedItem = query.ExecuteScalar().ToString();
        }

        private void UpdateBTN_Click(object sender, EventArgs e)
        {
            query = new SqlCommand($"select code_executor from executors where (surname_executor + ' ' + name_executor + ' ' + midname_executor) like '{ExecutorsCB.SelectedItem}'", dbhelper.sqlConnection);
            string codeExecutor = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select code_Driver from drivers where (surname_driver + ' ' + name_driver + ' ' + midname_driver) like '{DriversCB.SelectedItem}'", dbhelper.sqlConnection);
            string codeDriver = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select code_vehicle from vehicles where (brand_vehicle + ' ' + model_vehicle + ' ' + number_vehicle) like '{VehiclesCB.SelectedItem}'", dbhelper.sqlConnection);
            string codeVehicle = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select code_logistician from logisticians where (surname_logistician + ' ' + name_logistician + ' ' + midname_logistician) like '{LogisticiansCB.SelectedItem}'", dbhelper.sqlConnection);
            string codeLogistician = query.ExecuteScalar().ToString();

            query = new SqlCommand($"update requests set code_executor = @code_executor, code_driver = @code_driver, code_vehicle = @code_vehicle, date_loading = @date_loading, place_loading = @place_loading, fullname_shipper = @fullname_shipper, telephone_shipper = @telephone_shipper, name_cargo = @name_cargo, transportation_conditions = @transportation_conditions, date_unloading = @date_unloading, place_unloading = @place_unloading, fullname_consignee = @fullname_consignee, telephone_consignee = @telephone_consignee, route_town_request = @route_town_request, route_mileage_request = @route_mileage_request, transportation_format = @transportation_format, payment_request = @payment_request, payment_conditions_request = @payment_conditions_request, code_logistician = @code_logistician where code_request like '{MainMenu.requestIndex}'", dbhelper.sqlConnection);
            query.Parameters.AddWithValue("code_executor", codeExecutor);
            query.Parameters.AddWithValue("code_driver", codeDriver);
            query.Parameters.AddWithValue("code_vehicle", codeVehicle);
            query.Parameters.AddWithValue("date_loading", DateLoadingDTP.Value);
            query.Parameters.AddWithValue("place_loading", PlaceLoadingRTB.Text);
            query.Parameters.AddWithValue("fullname_shipper", NameShipperTB.Text);
            query.Parameters.AddWithValue("telephone_shipper", TelephoneShipperMTB.Text);
            query.Parameters.AddWithValue("name_cargo", NameCargoRTB.Text);
            query.Parameters.AddWithValue("transportation_conditions", TransportationConditionsRTB.Text);
            query.Parameters.AddWithValue("date_unloading", DateUnloadingDTP.Value);

            query.Parameters.AddWithValue("place_unloading", PlaceUnloadingRTP.Text);
            query.Parameters.AddWithValue("fullname_consignee", NameConsigneeTB.Text);
            query.Parameters.AddWithValue("telephone_consignee", TelephoneConsigneeMTB.Text);
            query.Parameters.AddWithValue("route_town_request", RoutePlaceTB.Text);
            query.Parameters.AddWithValue("route_mileage_request", RouteMileageTB.Text);
            query.Parameters.AddWithValue("transportation_format", TransportationFormatDTP.Text);
            query.Parameters.AddWithValue("payment_request", PaymentTB.Text);
            query.Parameters.AddWithValue("payment_conditions_request", PaymentConditionsTB.Text);
            query.Parameters.AddWithValue("code_logistician", codeLogistician);

            if (query.ExecuteNonQuery().ToString().Equals("1"))
            {
                MessageBox.Show("Изменение прошло успешно!", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.Close();
            }
        }
    }
}